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...

asked 02 Sep '21, 04:20

UnhandledException's gravatar image

UnhandledExc...
25112
accept rate: 0%


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.

permanent link

answered 02 Sep '21, 04:37

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 02 Sep '21, 04:43

3

> 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

(02 Sep '21, 07:12) Breck Carter
Replies hidden

...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...

(02 Sep '21, 14:06) Volker Barth

> 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.

(02 Sep '21, 14:58) Breck Carter

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.

(02 Sep '21, 17:04) Volker Barth

Prove it :) He did!

Am I now an approved evildoer? ;)

(03 Sep '21, 09:11) Volker Barth

(04 Sep '21, 07:50) Breck Carter
showing 1 of 6 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×23
×10

question asked: 02 Sep '21, 04:20

question was seen: 1,275 times

last updated: 04 Sep '21, 09:05