JSON format of parameter in Run SQL Query
Hi there,
I am trying to pass multiple values to an SQL query using an IN Where clause, for example
Select * from parcels where ID in ('123','456','789')
How do I format the String value to include in the JSON in the parameter?
Command text:
Select * from parcels where ID in ( @IDParam )
Parameters:
= { 'IDParam' : $value1.result }
Value1:
'123','456','789'
Thanks, I feel like I have tried everything!
David
-
The issue you're bumping into is how SQL parameterization itself works. Your parameter is brought into the query as a single string item, so all characters within it which might otherwise be SQL syntax to indicate multiple values (commas, quotes, etc) will be escaped such that the entire value is one string. This protects against SQL injection.
In other words, you queries with one parameter will always amount to
SELECT * FROM parcels WHERE ID in ( "<one string>" )
for example:
SELECT * FROM parcels WHERE ID in ( "'123','456','789'" )
There are two ways I know of to get around this. The first and most straightforward way, which I recommend against if possible, is to create a dynamic query using the Run SQL Query Unsafe activity. You will very much want to protect yourself against injection if possible here, I would do every bit of query generation possible within the server workflow. This is the way I'd probably go myself, but I hesitate to recommend it to non-SQL folks because it is very easy to expose your DB in undesirable ways using that activity so if you go that route be sure to protect the activity's inputs.
I'd pass in an array of IDs as a WF argument, then server-side I'd regex each ID to ensure that each character in each input character is an integer Then I'd generate a number of question marks to place in the Command equal to the number of IDs, which themselves would go into the Parameters as an array like ['123', '456', '789'].
SELECT * FROM parcels WHERE ID in (?, ?, ?)
The other option, which I recommend if possible just because it's less open to nefarious activity, would be to simply hard-code some maximum number of IDs to query. For this, I'd use positional inputs if possible (depending on your DB and connection provider), then you can input an array of values to Run SQL Query. You will need to pad out your input array to get this to work, either with known false values or you could repeat your input IDs until you hit the total max. If your max were 10 you would have a Command that looks like:
SELECT * FROM parcels WHERE ID in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
with Parameters like =['123', '456', '789', '999', '999', '999', '999', '999', '999', '999'] or =['123', '456', '789', '123', '456', '789', '123', '456', '789', '123']. AFAIK, you could do this to handle 2000 IDs if you wanted (depending on DB and connection).
0 -
Thanks Zack that is a superb response! Not the answer I was hoping for but I appreciate the workaround options. I think I will try the unsafe query. My client workflow will pass the ids as a string to the server workflow and return an excel file so it should be low risk.
David
0 -
David, do you mean that you'll be sending one string or an array of strings? If the former, I feel compelled to ask you to consider at the very least generating the list string in the server workflow.
Pre-generating SQL in the client to be concatenated directly into a SQL command opens up your DB to SQL injection, this is why the activity is called "Unsafe". For example, say I was an app user and I noticed the call to your WF with an input argument of "'123','456','789'" to create the SQL statement:
SELECT * FROM parcels WHERE ID in ('123','456','789');I could then replicate that call, except as a not-too-kind user I might send in a different argument like "); <whatever I feel like> (".
SELECT * FROM parcels WHERE ID in ('123','456','789'); DROP ALL TABLES; ();Now you have data anymore. IDK if you can use a SQL Query activity to DROP, I suspect not which is why VertiGIS separated the Run Query and Run Non-Query activities (notice how there is no option to throw an expression in the non-query, btw) but someone could
SELECT * FROM parcels WHERE ID in ('123','456','789') OR 1=1; ();Now I have data for all your parcels or
SELECT * FROM parcels WHERE ID in ('123','456','789') OR 1=0 UNION SELECT object_id, schema, schema_name(schema_id), name from sys.tables; ();Now I have all your table names and, if you're just returning the data table from the Unsafe SQL activity directly to the client, I grab every record of data accessible with the credentials connecting to your SQL database.
This is why the original server-side query activity is unable to take an expression as its Command text; VertiGIS is trying to prevent developers from opening these sorts of holes. Using the Parameters argument prevents this issue entirely as all inputs will be sanitized, preventing you from executing unexpected SQL; simply doing the string concatenation on the server would at least be a step in the right direction.
1 -
Thanks for following up.
I am passing an array of IDs like you said, then parsing that on the server workflow using a join to stitch the IDs together into a string. Then I will use that string in the command text.
evaluate1 = $getWorkflowInputs1.inputs.join("','")
Command Text = "Select * from parcels where ID in ('" +$evaluate1.result + "')"
That seems hard to inject anything suspect into since it will only get evaluated against the ID field.
David
0 -
I finally got it working and just wanted to update this thread.
The run workflow activity from the client sends the argument:
= { 'ids': $collectionIDs.result }In the server workflow, there is a get workflow inputs activity, then and evaluate activity:
=$getWorkflowInputs1.inputs.ids.join("','")Then the unsafe SQL query is:
="Select * from parcels where ID in ('" +$evaluate1.result + "')"I send the data table to the Convert Data Table to XLSX, then the Create Download activity, and return the URL which goes into a display form as a hyperlink.
David
1
Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.
Kommentare
5 Kommentare