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.
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.
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.
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?
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.
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.
(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.
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?
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!
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 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.
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?
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 = ?)ā;