Give me parameterized SQL, or give me death


#1

I have fairly strong feelings when it comes to the stored procedures versus dynamic SQL argument, but one thing is clear: you should never, ever use concatenated SQL strings in your applications. Give me parameterized SQL, or give me death. There are two good reasons you should never do this.


This is a companion discussion topic for the original blog entry at: http://www.codinghorror.com/blog/2005/04/give-me-parameterized-sql-or-give-me-death.html

#2

Actually, I would say the biggest drawbacks of parameterized SQL are a) many older implementations don’t support it, and b) large numbers of programmers don’t know about it.

This is a failure of education and training as much as anything else. SQL is vastly underrepresented in college courses, and IME most VB and Java programmers learn SQL on the job from reading code they were maintaining. As a result, the mistakes of the past get perpetuated.

I was in this situation myself when I first learned SQL, working in Access 97 VBA. Had I known abut parameterized SQL, it would have saved me a lot of effort - in addition to the other problems listed, concatenation is often difficult to get right. But I didn’t know it was an option, because the senior programmers where I worked didn’t.

Worse, most texts I’ve seen on SQL programming - especially for VB and Java - use concatenated SQl in their examples, and often never mention parameterized queries. Needless to say, this seriously compounds the problem.


#3

We use parameterized SQL after fuddling through a FixSQL function to remove unwanted characters. As you hint, that can only get you so far. Parameterized SQL is the only way to go and saves you from attacks. You’re lazy if you don’t use it…it’s simple to learn and easy to implement.


#4

I use the doubling the quotes mitigation method. I had thought it was nearly a rock solid technique but that article you included shows how it can be manipulated with MySQL. If they had a SQL Server or Infomix example of bypassing it that would have been better.

I’m kind of stuck between a rock and a hard place though, because I’m not in control of the data access methods. I get a wrapper object for a ado connection object. And it only has two methods, one to get a recordset back and one to just execute the given sql. Can I use an ADO command object just to give me the appropriate sql string without an actual connection?

Further, how can I be sure that parameterization does a better job of escaping the quotes in the string value? I think this just moves my security dependency to the ADO library. I can trust that the people who wrote ADO know more about interacting with databases then I do, but can I trust their method is more secure?


#5

There can be drawbacks from using parameterized queries as the execution plan is cached on the server. Since it is cached, the plan may be very non-optimal for varying values of the input parameters.

Consider a large table with an index. For certain values the index may be highly selective and an index scan can be used instead of sequential scan on the table. When you pass dynamic sql to the backend the optimizer has the information needed to estimate and choose whether or not to use the index. However, if you are using a prepared statement, the optimizer no longer has that information available when choosing a plan - so typically a conservative plan that uses a sequential scan will be used for all input values which eliminates any advantage that the index could have provided.

My $0.02 worth.


#6

Can some post a code sample where the full
REMOVAL of all single-quotes (from the user)
can still result in SQL injection?

(I need to get a string from the user… but
it’s never going to need to contain a single-quote character.)


#7

You guys need to look at LLBLGenPro. ORM mapper and all emitted SQL is parameterized. Awesome tool.


#8

Everyone is posting everything EXCEPT an
example of some code… that can still be hacked… even if I remove all single-quote
characters from the user’s input string.


#9

(I need to get a string from the user… but
it’s never going to need to contain a single-quote character.)

Everyone is posting everything EXCEPT an
example of some code… that can still be hacked… even if I remove all single-quote
characters from the user’s input string.

Noone is posting since the example would be pointless. If it was possible to do that it would be useless to the vast majority of cases where quotes are required.

In short, you have two options:

a) Use your system, but only if quotes are never needed, and hope it works.
b) Use paramaterised queries, which always work, all the time, even if quotes are there.

Its not a hard decision to make, is it?


#10

useless to the vast majority of cases where
quotes are required.

Huh? Quotes are required in zip-codes?
Phone numbers? Show sizes? Medical doses?
Time? Date? IQ scores? (hint, hint, hint)

I can only think of 1 field (last name) in our apps. Those
we WILL change. (Even though we don’t have
any employee that has a quote in their last name.) O’Brian, etc.

a) Use your system, but only if quotes are
never needed, and hope it works.

I’ve already said (over and over again) quotes
are NOT needed in our applications.

b) Use paramaterised queries, which always
work, all the time, even if quotes are there.

They will not work when it’s impossible for us
to hire the people, have the time, the budget,
the resources, etc… to search 100,000s of lines
of very old code… to fix something that can
be fixed simply by removing quotes.

Instead of telling us to spend $85,000… why
not just post the code that will break… if we
remove all quotes… and don’t use parameterized
queries?

THAT is the question here. Nothing more.


#11

== Give me parameterized SQL, or give me death

Give the moron death. You should shut up instead of trying to show you know something, when you don’t have enough experience to substantiate what you recommend. Try developing in the other platforms before you go and denounce them. Idiot!


#12

Try developing in the other platforms before you go and denounce them

Where did I denounce a platform? I have no idea what you’re talking about.

Everyone is posting everything EXCEPT an example of some code… that can still be hacked… even if I remove all single-quote characters from the user’s input string.

Hi Jill,

This is called “latent SQL injection”. What happens is, a sanitized string containing SQL gets inserted into a database field. Let’s say you inject SQL into your password…

“x’ OR full_name LIKE ‘%Bob%’”

This password doesn’t cause any SQL injection problems at the time of insertion; it gets written to the database as-is.

Later, when the application constructs a SQL string using the password field, IT ASSUMES THE PASSWORD FIELD IS A SAFE STRING, and the injection takes effect at that time. Not quite as effective, but still a valid attack in many cases.

I hope that answers your question.


#13

Jeff,

I agree with your example in the case of quote escaping, but fail to see how it would cause issues (beyond failing to match the altered password) in the case of quote stripping.

Beren.


#14

Jeff, the code is REMOVING all the quotes.
(Not escaping them.)

With all the quotes REMOVED… is SQL injection still possible? How? Give an example.

Thanks.


#15

How would I write a parameterized version of
this:

SELECT * FROM MyTable WHERE MyField IN (2,4,6,8)

This doesn’t seem to work:
SELECT * FROM MyTable WHERE MyField IN (@MyPars)

Thanks


#16

if I remove all single-quote characters from the user’s input string

Well, you could, but Bob O’Malley is gonna be pretty pissed about that. :wink:

How would I write a parameterized version of (an IN clause)

I’m not sure you can. But you have the same problem with stored procs too:

http://forums.devx.com/showthread.php?t=150247


#17

Any input must have its wrapper parameter to prevent SQL injection but parametrizing like this is dumb and takes control of you. In the end SQL server will obtain just string. Who the hell does guarantee that “smart” objects can’t contain an exploit?


#18

We use parameterized SQL query in all part that dealing with user’s input. And query string easier to read and maintain.
With ADO.NET, parameterized SQL is easy to use, as your example we can use @email syntax, no matter how many @email used in query, we just need to add @email to SqlCommand’s parameters collection one time.
string sQuery =”SELECT * FROM UserInfos WHERE (@email=’public@abc.com’) or (email = @email)“;
But recently We have some project in MFC using ADO. Can we use syntax @email like ADO.NET or just only way is to use “?” and have to add two parameters?
CString sQuery = ”SELECT * FROM UserInfos WHERE (@email=?) or (email = ?)“;

thanks.


#19

correct for second query string:
CString sQuery = ”SELECT * FROM UserInfos WHERE (?=’public@abc.com’) or (email = ?)“;


#20

I do not prefer to use parameters because:

  • It is just a little bit dificult to debug and hardens maintenance
  • Decreases portability between database systems.
  • Sometimes it is needed to log all executes SQL sentences as is.