How prepared statement protect again SQL injection in below statement

2018-07-03 16:03:49

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

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.

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.

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.

链接地址: http://www.djcxy.com/p/93800.html