38
Why parameterized SQL queries(nome = ?) previnem SQL Injection? Can cite examples?
3 answers
26
It seems to me you’re referring to Prepared statements.
What happens is that when informing a query with parameter for the connection library to the database, it treats the received value as a parameter, making all escapes transparently, making it impossible for a past value to modify the behavior of the query.
To use queries with strings concatenates need to do several checks. In PHP, for example, you need to check settings (e. g. magic_quotes_gpc) and/or calling functions as 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 only concatenates the strings would result in:
SELECT id FROM usuario
WHERE usuario = 'admin'
AND senha = '' OR '1'
The consultation would be modified and the invader could do login as an administrator.
With the Prepared statement the query would be executed in the bank with the proper escapes, preventing the attack:
SELECT id FROM usuario
WHERE usuario = 'admin'
AND senha = '\' OR \'1'
13
$user = "1;DROP TABLE Users;";
$SQL = "SELECT * FROM Users WHERE User=$user";
If the $user variable is something filled by the user and the user puts in its value something like the one I have in the example above, Mysql will run
SELECT * FROM Users WHERE User=1;DROP TABLE Users;
Which will result in a drop to the Users table.
When using PDO, or Prepared statements with Mysqli, the engine automatically prepares the variables to prevent this from happening, and the database is protected.
11
Following what said in a similar question in Soen, queries and with Prepared statements are sent to the database separately from the data so it is not possible to have two SQL queries (SQL injection).
However Prepared statements Not is a 100% guaranteed solution, to 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 next to clause ORDER, you should do the whole prevention work alone (sanitize the content, escape it) see here the links of this same stackexchange network: Prepared Statement 100% safe and PDO Prepared statements in ORDER BY
-
3
This answer is more precise so far. But see what loopholes SQL Injection will allow for much more than one query embedded in another ("two SQL queries"). Through SQL Injection you can also embed data handling commands, change Databases schema and can even drop entire tables and Databases. The first sentence "queries and with Prepared statements are sent to the database separately from the data" also maybe it is clearer if you exchange "data" for "parameters".
-
It is worth noting that this problem is PDO, because the Prepared statements are simulated. In Mysqli the native Binding is used, and the parameters are passed separately to the server
Browser other questions tagged sql security-guard sql-injection
You are not signed in. Login or sign up in order to post.
Why do all the answers so far only deal with the subject from the point of view of PHP? The question is very interesting, even better if there is an answer that does not relate to a specific language.
– Renan Gomes
But the problem and the reasons are the same at all, what changes is just how to use Prepared statements. In java it would be: Preparedstatement ps= null; Resultset rs = null; String query = ("select * from person Where nm_person = ?"); ps = connection.prepareStatement(query); ps.setString(1, 'maria'); Set resultres = ps.executeQuery();
– Mariana Sempe
This article explains how SQL injection works. http://www.guid.com.br/categorias/development/injecao-de-sql/
– user27522