Dynamic SQL & SQL injection

re: Dynamic SQL & SQL injection

wenchingchua — Fri, 05 Jan 2007 04:31:03 GMT

Hi, thanks for sharing. Is there anyway which I can prevent to write dynamic sql if i need to dynamic pass the table name? For example, there are 2 values in a dropdownlist - A and B. If I select A, then press search button, it will go back to my database and pass into a dynamic sql - e.g. tb_A_Logistics and perform some query. Same to the B. I cannot find a way to overcome this for a dynamic table name. Any help? Yes, I will like to prevent dynamic sql for my case, but I cannot find a better way. Thanks.

re: Dynamic SQL & SQL injection

raulga — Fri, 05 Jan 2007 05:32:10 GMT

If I understand your scenario, you construct the table name based on user input (from the dropdown list), correct? I am not aware of an alternative from dynamic SQL for your particular scenario. As far as I understand, statements such as SELECT cannot use a variable in the FROM clause to specify the table/view name, and it also makes this scenario one that cannot be easily parameterized.

I will strongly recommend in your case validating the input (i.e. make sure the value from the dropdown list follows your business rules) and make sure you properly delimit and escape the table name (in this case, as table names are sysnames, using QUOTENAME or an equivalent will be quite useful) in order to prevent SQL injection attacks.

I will appreciate if anyone knows an alternative mechanism without using dynamic SQL or a way to use a variable to specify the table name in such statements and wants to share the information.

Thanks a lot,

-Raul

re: Dynamic SQL & SQL injection

wenchingchua — Fri, 05 Jan 2007 09:49:48 GMT

Thanks for your feedback. Looking forward on that too if anyone can share some lights here :)

Передача параметров в динамический SQL. Продолжение.

Константин Косинский — Fri, 05 Jan 2007 10:02:50 GMT

Пару недель назад я написал в своем блоге о Передача параметров в динамический SQL . Где пытался показать,

re: Dynamic SQL & SQL injection

yiangos — Mon, 08 Jan 2007 16:02:00 GMT

Hello.

Well, I might have an alternative without Dynamic SQL, however it is of limited use, and with a lot of constraints.

Basically, what you select from either table must have the same structure (i.e. same datatypes) and there's only a predefined number of different tables you can use the solution on. Moreover, I'm pretty sure that performance-wise, this gives you quite an overhead.

Having taken all these into consideration, here goes:

Suppose you have two tables, tbl_A and tbl_B, with 3 columns that have the same datatypes (and probably the same meaning data-wise).

Suppose now, that you have the dropdown with the two values. This makes for the user input. After proper validation (i.e. that it is actually one of the allowed values) the input is passed as an argument to a function. The function is just a big if-else if:

if the argument is, say "A", then do one select (write out the full select statement, with the table name tbl_A hard coded).

Else if the argument is, say, "B", do another select (again, hard coded select statement, with tbl_B instead of tbl_A).

I guess you can see where this fails: if you want different datatypes in the output, depending on the table you select, this solution won't be of any help, as the return type of the function is a table with statically defined columns.

Also, performance-wise, I don't know what overhead this might give, so if you have any feedback, it's most welcome.

Anyway, hope it helps.

Fun and fumes about Dynamic SQL & SQL injection

SQL Server Transact-SQL (SSQA.net) — Tue, 24 Jul 2007 15:27:07 GMT

D-SQLInjection things you need to consider and take care, just caught my eye recently.

Guard against SQL injection in dynamic PL/SQL

dave^2=-1 — Fri, 01 Feb 2008 13:23:23 GMT

I am slowly coming to terms with Oracle again, after a decade or so of using SQL Server exclusively.

SQL Injection attacks

Troubleshooting and Tips - Cindy Gross — Wed, 25 Jun 2008 03:18:00 GMT

<p>This year SQL injection attacks are being stepped up and even automated against SQL Server. While SQL injection attacks can occur against any DBMS, my blog will only address SQL Server.</p ...