Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

Vulnerable to SQL Injection?

One of the things that troubles me most about SQL Injection is that is seems it is still very misunderstood.

Is the following psuedo-code vulnerable to SQL Injection?

String SearchTerm = {some user input here, unvalidated and unscrubbed -- uh oh !}
Command C = New Command("SELECT * FROM SomeTable WHERE Description LIKE '%' + @Match + '%'") 
C.Parameters("@Match") = SearchTerm
Return C.Execute()

Also, consider the following T-SQL code as well.  What do you think it will return? 

CREATE TABLE Letters (Letter char(1) primary key)
INSERT INTO Letters (Letter) VALUES ('A')
INSERT INTO Letters (Letter) VALUES ('B')
INSERT INTO Letters (Letter) VALUES ('C')
INSERT INTO Letters (Letter) VALUES ('D')

DECLARE @Letters VARCHAR(10)
SET @Letters = 'A,B,C'
SELECT Letter FROM Letters WHERE Letter IN (@Letters)

It may not seem like it, but there is a very key concept that these two examples share: the difference between code that is executed versus expressions that are evaluated.   The difference is crucial to understanding what SQL Injection is and where and when you should be worried about it (or not). 

(Edit: the red part above was missing from the post initially ... sorry about the confusion.  I also added some green color coding in the psuedo-code to help distinguish string literals from code)

Print | posted on Friday, April 21, 2006 9:34 AM | Filed Under [ T-SQL Techniques Security ]

Feedback

Gravatar

# re: SQL Injection-able or Not?

Jon - what you have described is absolutely *not* SQL injection. Thank you for the post, it definitely helps make my point about the common misunderstandings of how/when it occurs.
4/21/2006 10:36 AM | Jeff
Gravatar

# re: SQL Injection-able or Not?

Are you stating that string can not cause sql injection?
I'd double check that if I were you.
4/21/2006 10:44 AM | Jon
Gravatar

# re: SQL Injection-able or Not?

I don't see an injection problem in the first. Let's see if we can create one. Using the Northwind sample db and the sql from the post, this runs fine without trouble:

DECLARE @Match nvarchar(15)
set @Match = 'A''; select * from Orders --'
SELECT * FROM Categories WHERE Description LIKE '%' + @Match + '%'

Evaluation works as it should. To hit a problem, I actually has to paste @Match into the SQL:
SELECT * FROM Categories WHERE Description LIKE '%A'; select * from Orders --%'

Which in our our psuedo-code would be:

String SearchTerm = <User input >
Command C = New Command("SELECT * FROM SomeTable WHERE Description LIKE '%" + SearchTerm + "%'")
Return C.Execute()



4/21/2006 10:47 AM | James Curran
Gravatar

# re: SQL Injection-able or Not?

Sorry Jeff / James you are correct, my mind was focused more in on the client application of a dynamic SQL String in vb.

Something to this effect:

>SELECT * FROM Categories WHERE Description LIKE '%' & Match & '%'

The previous posted by James

SELECT * FROM Categories WHERE Description LIKE '%' + @Match + '%'

That would not cause a problem...sorry again...too quick with the shot this morning.

Thanks,
Jon
4/21/2006 10:59 AM | Jon
Gravatar

# re: SQL Injection-able or Not?

The funny thing is that I knew immedately that it's wasn't a problem (though I did spend some time trying to see what I might have overlooked), but the main thing is that it took me quite a while to convince myself it wasn't a SQL syntax error.

4/21/2006 3:14 PM | James Curran
Gravatar

# re: SQL Injection-able or Not?

Your example is not injectable because: The command string that will be compiled-and-executed does not include the user input.
The user input - because you made it an SQL parameter - will be evaluated, but never compiled for execution.
If it doesn't get compiled-and-executed, it can't be injected.
4/24/2006 4:24 AM | Chris F Carroll
Gravatar

# re: SQL Injection-able or Not?

Chris -- the first half is right, but your conclusion isn't 100% true -- using SQL parameters doesn't guarantee that you are safe from SQL injection.

What if the command had been

Command C = new Command("exec('select * from table where description like ''%' + @Match + '%''')")

(I admit that is pretty hard to read, hopefully it is clear)

Here, we have no concatenation being done at the client, and indeed at the server, all the initial concatenation is doing is creating a string -- it is simply an expression that is evaluated . So, we are still safe up to that point. However, that string is passed to the exec() function in SQL, which executes the string directly -- and right there we are in danger of injection. The only point is that be careful thinking that simply using parameters makes you 100% safe from injection.
4/24/2006 9:43 AM | Jeff
Gravatar

# re: SQL Injection-able or Not?

I don't know anything about abt command.execute. And I don't want to take a side on whether its injectable or not. Can you tell what will happen if I give @match as

"A%; drop table sometable --"
5/6/2006 1:54 PM | Can I play daddy!!
Gravatar

# re: SQL Injection-able or Not?

Same mistake as Jon :)
forgive the poor soul.
5/6/2006 1:57 PM | Can I play daddy
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET