|
Rob Grainger wrote: SQL injection attacks ... far too few coders seem to appreciate the effort required to adequately ensure that SQL statements ... adequately cleansed to prevent this.
It's not that hard, is it? Provided you know what the string delimter and escape characters are, there's no problem. For example, in SQL Server, escape all string parameter values by doubling up any single quote character (thus escaping them), and wrap the whole string in a pair of single quote characters (thus defining the limits of the string to the parser), and you're sorted.
|
|
|
|
|
...or just use Parameters instead - it makes the code easier to read as a bonus...
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together.
|
|
|
|
|
Could you supply example code of how to use parameters with an IN clause, where the number of items in the in clause is not known until runtime?
I've never seen a solution to that one...
|
|
|
|
|
Not without thinking about it for a while!
But if you are doing things like that, you should know what you are doing anyway rather than just suck-it-and-see which most seem to try. 
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together.
|
|
|
|
|
You might be thinking for a while. As far as I'm aware, it's not possible.
|
|
|
|
|
You can't use parameters with an IN clause.
You would have to use something similar to "WHERE ((col1 = @parm1) OR (col1 = @parm2)) ..." which is easy enough to build at runtime.
|
|
|
|
|
True, but it rules out stored procedures...
|
|
|
|
|
You could use table value parameters to pass multiple values in one parameter, I don't use stored procedures for most projects though.
Unless I am going to be performing the same query from 2 different applications or the query is extremely complex I always use paramaterized queries.
|
|
|
|
|
You could try using a table variable to essentially perform the operation.
Add the values to the table variable and join on the table. 
|
|
|
|
|
Or you could just use some standard SQL, with properly escaped parameters, which was my original point. Much simpler to develop and debug.
|
|
|
|
|
I disagree, I don't think you should be escaping characters yourself.
|
|
|
|
|
|
Different DBMS's have different escape characters and it's just not a nice way of doing it.
Paramaterized queries were created for a reason.
|
|
|
|
|
ScottM1 wrote: Different DBMS's have different escape characters
True. They also have different stored procedure syntax. Are you suggesting that's a good reason not to use stored procedures?
|
|
|
|
|
That's not what I'm suggesting at all.
If you were using stored procedures the procedure syntax would not change the way you call the stored procedures from your application.
If you were building SQL queries in your application and escaping it yourself different escape characters would force you to go through every single one and change it.
What happens if you forget to escape one of the arguments before you pass it?
Read this[^]
|
|
|
|
|
So if I change the database, I have to change one function (note: not "every single query") that escapes out strings. If you change database, you (possibly) have to rewrite every single stored procedure.
ScottM1 wrote: What happens if you forget to escape one of the arguments before you pass it?
Then you have a security bug.
Newsflash: If you don't write code correctly, it has bugs.
|
|
|
|
|
Electron Shepherd wrote: Then you have a security bug.
No, then YOU have a security bug, I don't have to worry about this.
The fact of the matter is you shouldn't be escaping characters yourself.
Parameterized queries were created for this purpose so why would you want to even do it yourself?
There are also performance benefits to using parameterized queries.
|
|
|
|
|
ScottM1 wrote: The fact of the matter is
Opinion, not fact.
ScottM1 wrote: so why would you want to even do it yourself?
And if you need to uyse and IN clause? What do you do then?
ScottM1 wrote: There are also performance benefits to using parameterized queries.
Really? As they say "state your source". Have you measured them?
|
|
|
|
|
Electron Shepherd wrote: Opinion, not fact.
Fact
Electron Shepherd wrote: And if you need to uyse and IN clause? What do you do then?
I already told you in a previous post.
Electron Shepherd wrote: Really? As they say "state your source". Have you measured them?
A parameterized query will use the same execution plan each time even if there are different arguments passed, adhoc statements will recompile each time different arguments are passed.
|
|
|
|
|
"The fact of the matter is you shouldn't be escaping characters yourself."
That is an opinion, held by you. You are perfectly entitled to hold that opinion. However, the one thing it is not is a fact.
ScottM1 wrote: A parameterized query will use the same execution plan each time even if there are different arguments passed, adhoc statements will recompile each time different arguments are passed.
Is that true? I don't see how it can be. I thought they were compiled each time, since a parameterised query is an ad-hoc query. All you are changing is how the variable parts of the query reach the DBMS.
|
|
|
|
|
It is true.
Parameterized queries result in prepared statements whose execution plan is re-used with different parameters, this is because it basically calls the system stored procedure sp_execute.
An adhoc query will only use the same execution plan if the query is exactly the same including all parameters and whitespace characters.
|
|
|
|
|
ScottM1 wrote: An adhoc query will only use the same execution plan if the query is exactly the same including all parameters and whitespace characters.
I can't speak for other DBMSs, but SQL Server is cleverer than that, and used auto-parameterization to allow different queries to reuse query plans. See http://www.benjaminnevarez.com/2010/06/auto-parameterization-in-sql-server/[^] for an example.
|
|
|
|
|
OK, I didn't know that it did that.
It will still only do it in a select few instances though, and looking at this[^] it will probably nearly never do it.
How often do you write a query that has no function calls, no GROUP BY statements, no sub-queries and also no joins?
|
|
|
|
|
ScottM1 wrote: How often do you write a query that has no function calls, no GROUP BY statements, no sub-queries and also no joins?
Apart from the joins bit, quite often. Single table queries only is a bit of a pain, though.
Interestingly, reading this[^], it looks like forced parameterisation removes the "single table" restriction.
|
|
|
|
|
That is interesting, the only downside appears to be that errors may be reported incorrectly.
I still think that if you want to query using parameters you should do it yourself, having the DBMS automatically changing your queries could end up in all sorts of funnys.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
