eeyo
asked on
SQL Server Dynamic SQL - SQL Injection Prevention
Understanding that Parameterized Stored Procedures are the best way to go ... having said that:
If I must use Dynamic SQL (SQL Server) with variable numbers of tables and fields, how "safe" is it for me to "Clean" the string by using regex (or other function) to ensure that:
1) the input string is limited to a short string (such as 15 characters to prevent buffer overflow errors)
2) only the characters A-Z, a-z, 0-9 and _ are used (no other symbols, semicolons, etc.)
Example SQL Statement:
Also, is using QUOTENAME for fieldnames and tablenames considered reasonably safe to prevent SQL Injections as well?
If I must use Dynamic SQL (SQL Server) with variable numbers of tables and fields, how "safe" is it for me to "Clean" the string by using regex (or other function) to ensure that:
1) the input string is limited to a short string (such as 15 characters to prevent buffer overflow errors)
2) only the characters A-Z, a-z, 0-9 and _ are used (no other symbols, semicolons, etc.)
Example SQL Statement:
"SELECT orderID where ProductDescription = '" & CleanedSQLText & "'"
Also, is using QUOTENAME for fieldnames and tablenames considered reasonably safe to prevent SQL Injections as well?
ASKER
Thanks for the code, but I was mainly asking the general question that if only letters and number (and underscore) are used in the input text, is it safe? In other words, if there are no "--", ":" ,"/", etc. is it pretty safe to assume that a SQL injection can't occur? The problem I see with using a blacklist is that it may too aggressive. For example, if an appropriate user input string is "I had to drop my cast on the table to begin to create my artwork", it wouldn't pass muster using a blacklist.
No, it is not. You need to check for a whole bunch of keywords etc.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Snarf0001, this works well for values. Do you know of any way I can also use parameters for specifying tables and/or fields (unfortunately, these can't be specified design time ... must be run time)?
No, without getting overly complicated there's not a simple way to accomplish that.
I would say best approach is to use both of the methods going on here.
For the actual value, use the parameterized query. Then the use can type in anything they need, and there's no worry about injection.
For the target table name, you would have to clean the input, but you can be a lot more restrictive as it only has to be a valid table name, so a LOT is excluded.
In your above sample, alphanumeric and underscore being the ONLY ones allowed, are spaces also excluded? If so, then I can't see that being open to injection.
But would be curious if @Shaun can think of something I'm overlooking.
I would say best approach is to use both of the methods going on here.
For the actual value, use the parameterized query. Then the use can type in anything they need, and there's no worry about injection.
For the target table name, you would have to clean the input, but you can be a lot more restrictive as it only has to be a valid table name, so a LOT is excluded.
In your above sample, alphanumeric and underscore being the ONLY ones allowed, are spaces also excluded? If so, then I can't see that being open to injection.
But would be curious if @Shaun can think of something I'm overlooking.
ASKER
Luckily, spaces are excluded for the table and field names.
Open in new window
http://www.c-sharpcorner.com/blogs/check-string-against-sql-injection-in-c-sharp1