Can I avoid all SQL-injection attacks by using parameters?
And don't worry about any thing in SQL injection in this case?
Or are there some types of these attacks which require more care on the part of the programmer?
views:
187answers:
6
+7
A:
No, you can't avoid all SQL injection attacks by using parameters. Dynamic SQL is the real issue, and this can occur in stored procedures as well as in your application code.
E.g., this is prone to a SQL injection attack: your parameterized query passes a username to a stored procedure, and within the stored procedure the parameter is concatenated to a SQL command and then executed.
For an example of many kinds of SQL injection attacks, see this SQL Injection Cheat Sheet. You will see that simply escaping single quotes is just scratching the surface, and that there are many ways around that.
RedFilter
2010-09-17 15:46:54
so what is the solution ?
M.H
2010-09-17 15:50:43
@MH: replacing all ' with ''
Quandary
2010-09-17 15:52:58
The solution is to never use dynamic SQL. If this is not possible, then you must go to extreme lengths to ensure that you properly escape any string that will be concatenated to a SQL statement, and also consider how the string is getting marshalled across domains (e.g., HTML --> Javascript --> URL encoding -->), as there may be additional complications due to those transformations.
RedFilter
2010-09-17 15:56:53
@Quandary: that is not sufficient. there are cases, such as when you're concatenating an integer, where there won't be any quotes around it. It is a non-trivial problem, and thus that is why parameterized queries exist. They solve 99% of the cases.
rmeador
2010-09-17 16:03:04
See the link I added to my answer that demonstrates injection attacks that do not use single quotes and thus would not be mitigated against, by naive replacement of quotes.
RedFilter
2010-09-17 16:05:06
`replace(replace(replace(replace(@yourstring,'update',''),'delete',''),'insert',''),'drop',''),...` and so on for every possible nefarious action you can think of. In other words, there's no real solution to protecting dynamic SQL.
Adam Robinson
2010-09-17 16:05:51
you can use parameters with dynamic sql...so it really does always come back to using parameters.
dotjoe
2010-09-17 16:16:45
@dotjoe: Using parameters is not enough. It is *how* they are used that is key. There is a world of difference between `set @sql = 'select ... where id=@ID'` and `set @sql = 'select ... where id=' + @ID`
RedFilter
2010-09-17 17:11:10
Well, your second example is not *using* parameters. That's just string concatenation.
dotjoe
2010-09-17 17:27:08
My example uses parameters for both cases. The first example uses an input parameter (although not made clear in sample, for brevity) as an embedded parameter; the second uses an input parameter as a concatenated parameter. My point is to show that just using parameters is not enough, you have to understand the specifics. If you are a developer writing code using stored procedures written by someone else, simply because you used parameters to pass data to the stored procedures does not mean the application is safe from SQL injection.
RedFilter
2010-09-17 17:48:30
parameters <> variables. I guess, to me, parameters in SQL world always means *input parameter* (if that is what it would be called). If I'm calling someone's stored procedure w/ parameters, I agree there is no way to tell if they are using the parameter correctly without looking at the SQL.
dotjoe
2010-09-17 19:46:52
+2
A:
If you are going to build a dynamic sql query with those parameters (passed to a stored procedure, for example) then there's a chance of sql injection if precautions are not taken.
Denis Valeev
2010-09-17 15:48:28
+8
A:
Yes and no. Yes, if all of your SQL statements are indeed static and use only parameters, then you're 100% protected from SQL injection attacks.
The problem comes when the parameters themselves are used to construct dynamic SQL statements. An example would be a stored procedure that generates a SQL statement dynamically for querying a multitude of different options, where a single monolithic statement would be impractical. While there are better solutions to this problem, this is a common one.
Adam Robinson
2010-09-17 15:49:50
+1
A:
You can always minimize the risk of SQL injection by using prepared statements, provided your database engine supports them.
Anyway, prepared statements is probably the most secure way of blocking SQL injections.
polemon
2010-09-17 15:55:02
+3
A:
Yes you can avoid all SQL-injection attacks by using parameters, as long as you use parameters exclusively all the way down the call stack. For example:
- Your app code calls a stored procedure or dynamic SQL in the database. That must use parameters to pass all values.
- The stored procedure or dynamic SQL internally constructs a call to another stored procedure or dynamic SQL statement. That must also use parameters to pass all values.
- Repeat ad-infinitum until you run out of code.
If you are programming in SQL Server, you can use sp_executesql to execute dynamic SQL, and it will let you define and pass parameterised values to the statement being executed.
Christian Hayter
2010-09-17 16:11:35
+1
A:
The problem is building the SQL statement dynamically.
For example, you might want to order the result based on the column the user selected. In most databases, you can't use parameters here ("ORDER BY ?" doesn't work). So you have to "ORDER BY " + column. Now, if "column" is a String, then the user of your web-application could inject code there (which is not easy, but possible).
Thomas Mueller
2010-09-17 16:56:10