Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Are Parameters really enough to prevent Sql injections?

I've been preaching both to my colleagues and here on SO about the goodness of using parameters in SQL queries, especially in .NET applications. I've even gone so far as to promise them as giving immunity against SQL injection attacks.

But I'm starting to wonder if this really is true. Are there any known SQL injection attacks that will be successfull against a parameterized query? Can you for example send a string that causes a buffer overflow on the server?

There are of course other considerations to make to ensure that a web application is safe (like sanitizing user input and all that stuff) but now I am thinking of SQL injections. I'm especially interested in attacks against MsSQL 2005 and 2008 since they are my primary databases, but all databases are interesting.

Edit: To clarify what I mean by parameters and parameterized queries. By using parameters I mean using "variables" instead of building the sql query in a string.
So instead of doing this:

SELECT * FROM Table WHERE Name = 'a name' 

We do this:

SELECT * FROM Table WHERE Name = @Name 

and then set the value of the @Name parameter on the query / command object.

like image 352
Rune Grimstad Avatar asked Nov 20 '08 20:11

Rune Grimstad


People also ask

Do parameterized queries prevent SQL injection?

Correct usage of parameterized queries provides very strong, but not impenetrable, protection against SQL injection attacks.

How do SQL parameters prevent SQL injection?

Parametrized queries This method makes it possible for the database to recognize the code and distinguish it from input data. The user input is automatically quoted and the supplied input will not cause the change of the intent, so this coding style helps mitigate an SQL injection attack.

Can SQL injection be prevented?

The only sure way to prevent SQL Injection attacks is input validation and parametrized queries including prepared statements. The application code should never use the input directly. The developer must sanitize all input, not only web form inputs such as login forms.

Is input filtering enough to stop SQL injection?

Is input filtering enough to stop SQL Injection? A common misconception is that input filtering and escaping can prevent SQL Injection. While input filtering can help stop the most trivial of attacks, it does not fix the underlying vulnerability.


2 Answers

Placeholders are enough to prevent injections. You might still be open to buffer overflows, but that is a completely different flavor of attack from an SQL injection (the attack vector would not be SQL syntax but binary). Since the parameters passed will all be escaped properly, there isn't any way for an attacker to pass data that will be treated like "live" SQL.

You can't use functions inside placeholders, and you can't use placeholders as column or table names, because they are escaped and quoted as string literals.

However, if you use parameters as part of a string concatenation inside your dynamic query, you are still vulnerable to injection, because your strings will not be escaped but will be literal. Using other types for parameters (such as integer) is safe.

That said, if you're using use input to set the value of something like security_level, then someone could just make themselves administrators in your system and have a free-for-all. But that's just basic input validation, and has nothing to do with SQL injection.

like image 151
Adam Bellaire Avatar answered Oct 09 '22 21:10

Adam Bellaire


No, there is still risk of SQL injection any time you interpolate unvalidated data into an SQL query.

Query parameters help to avoid this risk by separating literal values from the SQL syntax.

'SELECT * FROM mytable WHERE colname = ?' 

That's fine, but there are other purposes of interpolating data into a dynamic SQL query that cannot use query parameters, because it's not an SQL value but instead a table name, column name, expression, or some other syntax.

'SELECT * FROM ' + @tablename + ' WHERE colname IN (' + @comma_list + ')' ' ORDER BY ' + @colname' 

It doesn't matter whether you're using stored procedures or executing dynamic SQL queries directly from application code. The risk is still there.

The remedy in these cases is to employ FIEO as needed:

  • Filter Input: validate that the data look like legitimate integers, table names, column names, etc. before you interpolate them.

  • Escape Output: in this case "output" means putting data into a SQL query. We use functions to transform variables used as string literals in an SQL expression, so that quote marks and other special characters inside the string are escaped. We should also use functions to transform variables that would be used as table names, column names, etc. As for other syntax, like writing whole SQL expressions dynamically, that's a more complex problem.

like image 37
Bill Karwin Avatar answered Oct 09 '22 22:10

Bill Karwin