[Duckington]

Question:

I know from a lot of googling and forum reading what Prepared statements do, but I would like to know "how" they do it... "How" it is more secure than say, just making sure you escape all your inputs and use correct quotes, etc..

Let's say you do something like:

$st = $db->prepare("SELECT * FROM `table` WHERE `id` = :id");
$st->bindParam(':id', $_POST['id']);
$st->execute();

Or whatever query, doesn't matter for the sake of example.

So my question is...what does it actually do that is more secure than doing it yourself? Surely towards the endpoint of binding the parameter and executing the query it still has to actually build the sql query with the actual value (or parameter) in it (which could contain an injection)?

This is what is confusing me...

Or does it literally just do the escaping and such for you and nothing else? So that you don't have to rely on the user to remember to do it every time?


I could be way off, but I was assuming it was basically doing something like:

SET @id = "whatever";
SELECT * FROM `test` WHERE `id` = @id

And just ensuring the value in the @id variable is properly escaped and quoted...

Or...what?

Cheers.

[Dormilich]

the keyword here is content-code-separation. you have a fixed SQL Template Statement that doesn’t contain content (or at least no content that is not hard-coded into the statement). This is sent to the SQL server to be parsed. after that (in a separate transmission) the (userland) data are sent over. since SQL parsing is already done (and will not be repeated for that statement) any data content that might be a SQL statement (or part thereof) is treated as data and directly inserted into the DB.

[Duckington]

Still don't really get it, but thanks.

[CTphpnwb]

Think of it this way:

Using a traditional MySQL query you send instructions and data to the database server in one bundle of information. The server needs to interpret that bundle to determine which parts are instructions and which are user supplied data. SQL injection relies on confusing the interpreter into executing data as if it were valid instructions.

Using prepared statements you tell the server what the instructions are and then you send the user data. Because it already knows what the instructions are the server cannot be fooled into executing data.

This post has been edited by CTphpnwb: 14 June 2012 - 11:28 AM

[Duckington]

OKay I think I get it now. But what about injections which don't change the instructions, e.g.

SELECT * FROM table WHERE ID = ' ' OR 1='1'

What is it doing then which is any different from escaping them and quoting them properly yourself?

Or does the "OR" count as an instruction?

This post has been edited by Duckington: 15 June 2012 - 03:12 AM

[Dormilich]

let me put it this way

-- your query as prepared statement:
SELECT * FROM mytable WHERE id = ?;

-- assuming it would matter if we use ' or "
-- the statement would translate to something like
SELECT * FROM mytable WHERE id = "' OR 1='1";
-- but in fact, you do not execute it as such

This post has been edited by Dormilich: 15 June 2012 - 03:46 AM

[RudiVisser]

Prepared statements are compiled in the database engine before your data goes anywhere near them.

If you had a table like:

MyAwesomeTable (
   `id` INT(11),
   `myawesomeuser` VARCHAR(50),
   `myawesomepassword` VARCHAR(50)
)

.. and prepared this statement:
SELECT * FROM `MyAwesomeTable` WHERE `myawesomeuser` = ? AND `myawesomepassword` = ?

The database engine compiles it, and knows that parameter 1 and 2 are strings. When you give parameter 1 a string of any value, it's just used with the prepared statement, not needed to be escaped or anything since it's not parsing the statement, it's already done and is already compiled.

To explain further, with the above scenario, when you call that prepared statement with parameters, it is NOT internally translating it to this:
SELECT * FROM `MyAwesomeTable` WHERE `myawesomeuser` = 'Rudi' AND `myawesomepassword` = 'This is an \'awesome\' password';

Of course, that's what it seems like it's doing, but the main logic of your statement is compiled ("prepared") in the engine and you're simply pushing data in. Think of it like calling a method, your statement is the method and it's just doing something with the data you pass in.

I think that's the best way I could explain it, maybe confused things even more

This post has been edited by RudiVisser: 15 June 2012 - 07:31 AM

[CTphpnwb]

When using prepared statements you don't even quote the data. That makes sense because quoting it is an attempt to make it clear to the database what is data and not instruction, but prepared statements already "know" this.

As a prepared statement your query would look like this:

SELECT * FROM table WHERE ID = ?

and the data would be sent later. Note that all of the below would be treated as data, so none of it would alter the query in any way. It's not just the OR, but the single quotes that are data too!

' ' OR 1='1'

This is really just another example of the importance of keeping executable code and data segregated. While mixing them is certainly possible, it causes problems whether you do it with PHP and HTML, MySQL and user supplied data, or with any other language and what is data in its scope.

[Duckington]

Okay, thanks everyone. I think I get it now.