Validating parameters for malicious SQL when building dynamic SQL

Question

I have a situation where I must use dynamic SQL as its appearing difficult to use parameters.

The query I will execute dynamically is as below. What is the best way to validate the string in @sql for malicious SQL? I need to allow valid strings like 'CountryId = 23' but not allow strings like 'delete from Countries'. I do not want everything to be NOT ALLOWED.

I am using C# to run this query against SQL Server 2008 R2.

Perhaps, I could validate at SQL level or at C# level, but I am unsure which would be better.

SET @sql = 'SELECT * from Customers 
  where ' + @filterExpression + 
  ' order by ' + @sortExpression;

EXEC sp_executesql @sql

UPDATE: Maxim's answer seems to be the best for situations where you must use dynamic SQL, though dynamic SQL like the code I have in this post should be avoided as much as possible.

I came up with some validation on the variable @filterExpression in T-SQL to validate for malicious SQL code, which may still miss some malicious cases, but here is the T-SQL I came up with.

IF PATINDEX('%DELETE %',  @filterExpression ) > 0 
  OR PATINDEX('%SELECT %FROM%',  @filterExpression ) > 0 
  OR PATINDEX('%;%',  @filterExpression ) > 0 
  OR PATINDEX('%DROP %',  @filterExpression ) > 0 
  OR PATINDEX('%CREATE %',  @filterExpression ) > 0 
  OR PATINDEX('%TRUNCATE %',  @filterExpression ) > 0 
  OR PATINDEX('%sys%',  @filterExpression ) > 0
  OR PATINDEX('%INDEX %', @filterExpression ) > 0
  OR PATINDEX('%UPDATE %', @filterExpression ) > 0
  OR PATINDEX('%INSERT %',  @filterExpression ) > 0
  OR PATINDEX('%EXEC %',  @filterExpression ) > 0
  OR PATINDEX('%EXECUTE %',  @filterExpression ) > 0
  OR PATINDEX('%CAST%',  @filterExpression ) > 0
  OR PATINDEX('%DECLARE %',  @filterExpression ) > 0
  OR PATINDEX('%CONVERT%',  @filterExpression ) > 0
BEGIN
  RETURN;
END

How did you deduce it is "impossible to use parameters"? It's the same.. but, better. — Simon Whitehead
– Simon Whitehead, Commented Sep 29, 2013 at 22:35
I meant, that its not easy to find a parameters based approach in the situation I am coding against and I have tight deadlines for this task. You have some sample code in SQL to validate? — Sunil
– Sunil, Commented Sep 29, 2013 at 22:37
Easy - bool IsSlqEneteredByUserMalicious(string sql) { return true;}... I'd strongly recommend reconsidering design of your tool OR ignoring security issues altogether (which is perfectly fine if user already have admin rights) — Alexei Levenkov
– Alexei Levenkov, Commented Sep 29, 2013 at 22:38
The only way to validate it yourself is to write a full SQL grammar parser. Even then you can't trust it. What seems more suitable for your tight deadline? A SQL grammar parser or parameterized queries? — Simon Whitehead
– Simon Whitehead, Commented Sep 29, 2013 at 22:39
What would be the code inside the method IsSlqEneteredByUserMalicious( string sql)? — Sunil
– Sunil, Commented Sep 29, 2013 at 22:39

Maxim Balaganskiy · Accepted Answer · 2013-09-29 22:52:36Z

1

You could use a read only user, this would at least block any updates.
Look for keywords that look suspicious to you: INSERT, UPDATE, etc.
Use third-party libraries like this

Here is the good bunch of anti injections techniques.

edited Sep 29, 2013 at 22:52

answered Sep 29, 2013 at 22:46

Maxim Balaganskiy

1,57414 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sunil Over a year ago

Maxim - Thanks for the answer. I like the approach 1, where I would use a readonly userid in SQL Server connection string, and approach 3 also seems nice, which I am looking into now.

Collectives™ on Stack Overflow

Validating parameters for malicious SQL when building dynamic SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related