How do I pass a where clause as a parameter to a database procedure

[former_SQLA_member1695086]

Is there any way to pass a where clause generated in my source code as a parameter to a database procedure? Unfortunately it doesn't work but would be really cool ;-). Maybe there is a trick? I'm not that familiar with SQLAnywhere...

[VolkerBarth]

Can you elaborate what you have tried (a SQL code snippet or the like)?

Stored procedures are code blocks, not queries, and as such do not "have a WHERE clause" but you can either provide conditions via regular procedure parameters, or you can add a string as parameter that is added to a query within the procedure's code and executed via EXECUTE IMMEDIATE WITH RESULT SET ON.

Here's a simple sample from the docs - in this case not for a SELECT statement with a dynamic WHERE clause but for a CREATE TABLE with a dynamic table name, but you should get the point.

Take care to protect such dynamic SQL use against SQL injection...

---

If you are using SQL Anywhere 17, you might also look at its enhancement via "Indirect identifiers" - those can help to avoid dynamic SQL and might be helpful when you want to provide a WHERE clause with, say, just one value for a varying column.

[Breck_Carter]

> Take care to protect such dynamic SQL use against SQL injection

Always specify the EXECUTE IMMEDIATE ... WITH BATCH OFF when you are expecting to execute a single SQL statement. It prevents evildoers from injecting multiple statements via text substitution.

Update: As noted by Volker below, the WITH BATCH OFF clause may prevent a large class of SQL injection attacks (e.g., Little Bobby Tables) but it is not sufficient to prevent all attacks.

IMO EXECUTE IMMEDIATE user substring substitutions should be limited to individual field values and operators that can be checked for validity, rather than arbitrary predicates and expressions; e.g.

WHERE [select column] [select operator] [enter value]

rather than

WHERE [enter arbitrary predicate]

...the key words are limited and checked

[VolkerBarth]

...though evildoers could also manipulate data by enhancing a single SELECT statement via calls to functions/procedures with side effects or by constructing a SELECT over a DML statement, s preventing a batch is good but probably not sufficient...

[Breck_Carter]

> could also manipulate data

Prove it 🙂 He did!

Presumably the EXECUTE IMMEDIATE is constructed so that only substrings can be substituted, not the whole statement... WITH BATCH OFF would then eliminate the insertion of a CREATE PROCEDURE statement.

Similarly, it is unlikely a substring substitution could be used to add or modify an FROM clause to add a DML statement.

[VolkerBarth]

Well, the OP asks to dynamically supply a WHERE clause. Imagine the procedure would expect a real WHERE clause (excluding the WHERE keyword) as string that would be added to a SELECT... FROM...WHERE " built in the procedure. An evildoer might supply the conditions

• "dbo.sp_delete_directory('c:\\') = 0" or
• "EXISTS (SELECT * FROM (DELETE MyTable) REFERENCING (OLD AS gone))"

All legal SQL conditions but probably not without side-effects. Or course appropriate restricted privileges will prevent those, but it would not be uncommon that a user may be allowed to delete or update some tables.

[VolkerBarth]

Prove it 🙂 He did!

Am I now an approved evildoer? 😉

[Breck_Carter]