[cupidvogel]

Hi, I am reading everywhere that unlike mysqli, mysql doesn't support prepared statements. But I wonder how's that. Prepared statement is making a query statement with some user input value, right? So if $value contains a value input by the user, we can just make a double-quoted statement like "Select * from table where value = $value", and passing this statement to mysql_query returns results just as expected. So what's the deal?

[macosxnerd101]

A Prepared Statement does a lot in the background that the mysql family of functions does not handle. Prepared Statements sanitize the inputs, guarding against SQL Injection. They are also cached on the SQL Server. So when new parameters are bound to the query, it is more efficient to run the query again. The mysql family of functions does not support parameter binding in this manner.

[cupidvogel]

Ok, perhaps the mysql family doesn't cache the results. But the sanitizing can be done by special PHP functions and regular expressions, right? That should prevent injection. So barring the caching part, and the extra tedious bit of coding required to sanitize the user input which comes by default in mysqli, is it wrong to say that mysql supports prepared statements just as mysqli?

[macosxnerd101]

The checks you can do with the mysql family of functions still do not guarantee that your data has been completely sanitized.

Quote

So barring the caching part, and the extra tedious bit of coding required to sanitize the user input which comes by default in mysqli, is it wrong to say that mysql supports prepared statements just as mysqli?

No, this is not correct. You are ignoring everything that makes a prepared statement. The mysql family of functions do not support prepared statements.

[cupidvogel]

macosxnerd101, on 20 May 2012 - 07:45 PM, said:

No, this is not correct. You are ignoring everything that makes a prepared statement. The mysql family of functions do not support prepared statements.

Okay then, please explain what is meant by prepared statement.

[macosxnerd101]

I did already above. The Wikipedia page and PHP Documentation are good places for further reading.

[GunnerInc]

First off, Prepared Statements are compiled to byte code ONCE so if you are going to use the same statement over and over you gain a bit of speed.

Second, it protects you a bit from injection, the compiler sanitizes the query for you. In this respect is is wise to use prepared statements.

Damn,

This post has been edited by GunnerInc: 20 May 2012 - 12:54 PM

[cupidvogel]

Yeah, they corroborate to what I said above, don't they? The script has some boilerplate query, like Select * from foo where value = '?', and then that is executed like $stmt->execute(array($val)), right? How different is that from Select * from foo where value = $val ?

GunnerInc, on 20 May 2012 - 07:54 PM, said:

First off, Prepared Statements are compiled to byte code ONCE so if you are going to use the same statement over and over you gain a bit of speed.

Second, it protects you a bit from injection, the compiler sanitizes the query for you. In this respect is is wise to use prepared statements.

Damn,

Yeah of course, it is wiser and more efficient, but because of that can we say that mysql doesn't support prepared statement? Yes, it would require more coding to tie up the loose ends to sanitize user inputs, and it would take more time too, but it would ultimately achieve the same thing, right?

[macosxnerd101]

It deals with parameter binding. A Prepared Statement is similar to a function in this manner. You can bind new parameters to the statement in a similar way as you invoke a method with a different set of parameters. Because the Prepared Statement is cached, it runs more efficiently the second time around.

Quote

Yeah of course, it is wiser and more efficient, but because of that can we say that mysql doesn't support prepared statement?

Because of everything a Prepared Statement does that the mysql family of functions do not support means that they do not support prepared statements. The documentation will confirm this.

[cupidvogel]

Okay. Thanks.

[CTphpnwb]

I'd like to add that computer scientist have for decades been working to separate data and code for both better organization/readability and security. That's why we have so many different file types, as well as prepared statements! If you use the (more modern) tools the way they were intended to be used then things will be much easier for you, your users, and anyone who has to maintain your code.