How prepared statement protect again SQL injection in below statement

1
votes

I have gone through various document (SO post as well) about how exactly Prepared statement of PDO protect user from SQL injection.

Although,I understand it protect user because in prepared statement,user record is directly not executing on server insted we are sending positional / named parameter ( ? / :name) and then we send actual data in execute statement, and because of that it saves us from SQL Injection.

Well, Now if I have below code for SQL :

$query = "select * from user where id = $user_input_id";

and user input id = 1

So query will be something like :

$query = "select * from user where id = 1";

This is perfect till now. But if user entre $id = "1; DROP TABLE users;" so query will be something like :

$query    = "SELECT * FROM users where id=$id";

and hence ,it will execute 

$query = "SELECT * FROM users where id=1; DROP TABLE users;";

It works and out user table will drop because this query directly execute:

Well,I have read that prepared statement can save user from this :

and prepared statement working like :

$data = "1; DROP TABLE users;"

$db->prepare("SELECT * FROM users where id=?");

$db->execute($data);

In execute statement as well,record with Drop table is passing,so how exactly it won't execute drop table statament ? execute also performing some part on server right ?

Anyone can please explain how exactly prepared statement here save user from SQL injection ?

Thanks

3
phppdoprepared-statement
In prepared statement, when executed, the whole 1; DROP TABLE users; is treated as data, SQL searches the table for a record with id equals to 1; DROP TABLE users;, so no DROP query is executed. - Passerby
It's because it doesn't interpolate the value into the SQL string. Think of the statement like a function with arguments. When executed on the MySQL server, the DBMS will pass the parameters in as their nominated type (defaults to string) - Phil
@ Passerby so you mean,what execute statement will try to search is there any column name exist in database which you passed in prepared statement ? (in our case it is id) and on found id in database it will execute only id = 1 part of whole $data string and ignote DROP TABLE USERS statement (it is because it is stated after ";") ? and this way only require query executed and automatically removed portion of data added after ";" which can injected to your query ? Am i correct to understand ? - CodeWithCoffee

3 Answers

2
votes

Without explicitly setting a type (see PDOStatement::bindValue() for an example), it will treat the passed value as a string, so it will do this effectively:

SELECT * FROM users where id='1; DROP TABLE users;'

Btw, this would actually happen if you're using emulated prepared statements (PDO::ATTR_EMULATE_PREPARES); without this, it will send the parametrised query first followed by the actual data.

1
votes

That is why you can additionally set the type of binded data to the type you need. 

$stm->bindParam(":id", $id, PDO:PARAM_INT)

Additionally, PDO does some escaping of the data, and the string you provided will not break the query at ;, but will be inserted as plain string in the db.

1
votes

SQL injection is an attack against the SQL parsing step, not the statement execution step. In this, it has similarities to other parse attacks such as cross site scripting and XML injection attacks.

SQL injection works because the common (broken) technique of creating SQL statements by using string concatenation operators to combine both code and (untrusted) data in a single string allows for the possibility of a specially crafted string to violate the statement data protocol (typically by breaking out of a data context using string delimiters embedded in data), and allowing the attacker to manipulate the SQL parser into executing different code to that originally intended.

When one uses a prepared statement, one is telling the parser 'treat the statement purely as trusted code, and provide some slots into which I will insert the data for execution'.

When you drop the string '1; drop table users' into the data slot you created using the '?' placeholder, that string is not processed by the parser, and hence it has no opportunity to influence the parsing of the string : you made it impossible for the contents of the string to break out of a data context.

Using your example, the database will execute the equivalent statement to :

SELECT * FROM users where id="1; drop table users;"

This is a perfectly valid select statement, which may or may not return rows depending on the data in your tables, but which is almost certainly not going to work properly.

Nevertheless, the approach bypassed the attempt at SQL injection.

Be aware : using prepared statements is the ONLY generalised way to avoid SQL injection attacks. In general, attempts to filter untrusted input data are broken.