Why parameterized SQL queries (name =?) prevent SQL Injection?

36

Why parameterized SQL queries ( nome = ? ) prevent SQL Injection? Can you cite examples?

    
asked by Paulo Costa 12.05.2015 в 13:24

3 answers

25

I think you're referring to prepared statements .

What happens is that when you report a parameterized query to the database connection library, it treats the received value as a parameter, making all escapes transparently , making it impossible that a passed value modifies the behavior of the query.

To use concatenated strings queries, you need to perform multiple checks. In PHP, for example, you need to check configurations (e.g. magic_quotes_gpc ) and / or call functions like mysql_real_escape_string() and addslashes() .

Example in PHP using a login query:

$statement = $pdo->prepare('SELECT id FROM usuario WHERE usuario = ? AND senha = ?');
$statement->execute(array(
    "admin",
    "' OR '1",
));

A query that would only concatenate strings would result in:

SELECT id FROM usuario
WHERE usuario = 'admin'
AND senha = '' OR '1'

The query would be modified and the attacker would be able to log in as an administrator.

With the prepared statement the query would be executed in the database with the appropriate escapes, preventing the attack:

SELECT id FROM usuario
WHERE usuario = 'admin'
AND senha = '\' OR \'1'
    
12.05.2015 / 13:35
12
$user = "1;DROP TABLE Users;";
$SQL = "SELECT * FROM Users WHERE User=$user";

If the variable $ user is something filled by the user and the user puts something like the one I have in the example above, MySQL will execute

SELECT * FROM Users WHERE User=1;DROP TABLE Users;

What will result in a drop to the Users table.

When using PDO, or prepared statements with MySQLi, the engine automatically prepares the variables in order to prevent this, and the database is protected.

    
12.05.2015 в 13:41
7

Following what is said in a similar question in the SOen

/ a>, queries and with prepared statements are sent to the database separately from the data so it is not possible to have two SQL queries (the SQL injection).

However Prepared statements No is a 100% guaranteed solution, in cases where it is not 100% effective or where it is not possible to use Prepared Statements.

EX: PHP Data Object (PDO) does not support prepared statements along with the ORDER clause, you should do the prevention work all by yourself (sanitize the content, escape it) here the links of this same network stackexchange: Prepared Statement 100% Safe and PDO Prepared statements in ORDER BY

    
12.05.2015 в 13:39