Prevent SQL injection - why should you avoid typing if using prepared statements?

Question

I do some research on Internet security, and my article review says:

"It should be clear that an application must use prepared statements, stored procedures, and escape input to avoid SQL injection."

My question is: is one of these methods not enough? Well, prepared statements or stored procedures are better than simple, but if I use PDO, why should I avoid input or have a stored procedure? It makes sense?

Answer 1

I would change the wording of the revision as follows:

It should be clear that to exclude SQL Injection, an application must use prepared statements, an escape input, or filter application data before interpolating into an SQL string.

There is no need to avoid the value if you are going to pass it as a parameter. In fact, you should not, because you will enter a backslash in your data.

You need to interpolate the rows into your SQL statement if you cannot use the query parameter. Examples include:

• Table names and column names that have their own syntax for delimited identifiers . They must be part of the SQL query during preparation, so RDBMS can analyze and validate them.

• SQL keywords that should be sanitized but cannot be escaped because they are unlimited.

• Another syntax or expression.

• Some cases where literal values ​​should be indicated during preparation, for example. MySQL full text functions do not support parameters for the search pattern.

Stored procedures are not protection against SQL injection. You can prepare and execute unsafe dynamic SQL statements inside a stored procedure. See http://thedailywtf.com/Articles/For-the-Ease-of-Maintenance.aspx for a great story about this.

I look at all of these cases in my presentation of SQL Injection Myths and Fallacies . This may be a useful resource for you.

I also discuss SQL injection protection in the chapter of my book, SQL Antipatterns: Preventing Database Programming Errors .

Answer 2

If I use PDO, why should I [sc] insert a tab or save a stored procedure?

As long as you always use PDO, I see no reason to worry about escaping input or SP.

Answer 3

If in doubt, ask yourself: will this piece of simple input be screened by any API down the line? Most of the time they will be, unless you manually create SQL statements from the input.

You should not run away if you use PDO. You should not avoid if you are using JDBC prepared statements with parameters. Similarly, most other APIs will also take care of this. Stored procedures are not even associated with escaped data, and using them will not magically avoid SQL injection security issues if the input is not escaped into SQL that executes this procedure.

Always the SQL-Escape data that you put in SQL statements. Never delete SQL-Escape data outside of SQL statements.