Using prepared statements in PHP

Hi everyone, I’ve just come accross prepared statements for the first time. I’ve managed to implement a simple user authentication script by putting together a few tutorials I saw, but I can’t say this has really given me a proper handle on it.

What I’m really looking for now is a pretty comprehensive resource (or resources) that I can use to learn more about this, so I’m looking for suggestions. Ideally, I’d like a book that covers this stuff - I’m just more of a textbook person!

Please note, I’m not really looking for “try Google” style of comments. I’m more looking for recommendations of things that other people have found useful for them.

Thanks for any suggestions!

I know you don’t want to hear the “try google” comments but that’s the best advice.

We can’t really give you an example taylored to your needs, otherwise you’d simply not learn. That said, I can point you in the right direction:

PHP: Prepared statements and stored procedures - Manual

I think most of what a developer needs to know about prepared statements boils down to understanding when to use it and how to use it.

Prepared Statements offer two benefits:

  • When you are running the same query against the database multiple times with different values, then it makes sense to send the ‘identical’ part of the query first and then send only the ‘dissimilar’ parts (values) in subsequent request. This saves you from having to send entire query over and over again when only thing that have changed is values in the query. This makes things faster. Moreover, the database needs to parse the query only once which speeds up things further. The important point here is that not all queries in an application need to be issued multiple times. Prepare statements offer no benefits for ‘one-off’ queries.
  • There is a distinct category of security flaw that is caused when database mixes up ‘syntax’ part of the query with ‘value’ part and cannot tell them apart (SQL Injection). Prepared statements enable you to prevent such confusions by allowing your database to process the ‘syntax’ part of the query first and the ‘value’ part later. The important point here is that not all queries are susceptible to SQL injection.

Thus, it follows that you should use prepared statements when you are executing a query multiple times with different values or when your queries are vulnerable to SQL injection.

How to do it in PHP is even easier:

  1. Use PDO
  2. Create a statement with PDO:: prepare()
  3. Now supply value to the statement with PDOStatement::bindParam().
  4. Execute the statement.

For more details, follow this page in the manual for everything you need to know about using prepared statements in PHP.

I learned from the guy who wrote the PDO extension, Wez Furlong.

Slides: PHP Data Objects

You might hear the talk The ZendCon Sessions Episode 21: PDO: PHP Data Objects | PHP Podcasts or [url=http://devzone.zend.com/article/4765]The ZendCon Sessions Episode 21: PDO: PHP Data Objects

@gRoberts, I don’t actually have specific needs! This isn’t for a project, this is just because I’d like to increase my own knowledge. I did make my little login thing for a porject, but I’m now in that position where I’ve got a fully functioning something that I don’t completely understand - I’m not a big fan of that! :stuck_out_tongue:

@Kailash, thank you for that overview. It is clear and concise, and is probably the best summary I’ve seen - many others are not as complete, or get a bit too technical with language.

@Cups, thanks for the links. I haven’t had a chance to get very far through these yet, but it seems very useful.

I’m getting the sense that everyone would recommend using the PDO class when using prepared statements, any comment on this?

If you are looking to access MySQL database from your PHP application, there are 3 ways to do it:

  1. mysql extenstion
  2. mysqli extension
  3. PDO extension

You can use any of the above extensions all of which are normally available by default with your installation of PHP.

Mysql extension is the oldest of these extensions. You know that you are using mysql extension when you are accessing your database with functions that look like mysql_*. For example, mysql_connect, mysql_query, e.t.c.

The only trouble with this extension is that it hasn’t quite caught up with the advanced features of MySQL that were made available in more recent years. Therefore this extension is severely limited in terms of mysql features it allows you to use from your application. Prepared statements are one of the features not supported by this extension.

Thus, if you need to use more advanced features like prepared statements from your application, you have to use the other two extensions available in PHP, i.e. mysqli and PDO extensions. These are both modern and more matured extensions that you can use for accessing MySQL from PHP.

mysql and mysqli are very similar in terms of the functions they offer you. For example, for mysql_connect in mysql extenstion, there is mysqli_connect in mysqli. So everytime you use function that looks like mysqli_*, you are using the mysqli extension.

PDO is a different beast. It not only enables you to access most modern features of MySQL, it also allows you to use same functions to access a number of different databases by providing you the consistent APIs . Theoretically, if you want to switch your database from, say, MySQL to PostGreSqL, you could do with just a fewer tweaks. You don’t have to go all over place and rewrite every mysql_* function into pg_* function.

Thus, If you want to use prepared statements, your choices boil down to either mysqli or [URL=“http://php.net/manual/en/pdo.prepared-statements.php”]PDO.

I’d say yes, PDO is the way to go for two reasons:

  1. $statement->exec can accept an array of values, which is often easier to deal with (and is certainly less code) than a bunch of bindparam lines. You get up to fifteen values, bindparam can really start to get annoying.

  2. PDO isn’t JUST for mySQL. You can target all sorts of different database engines from the same code.

I’ve been pecking away at my own CMS for a while now, and one of the big things I’ve done is add an array to my PDO extension called “queries” – when I connect I figure out which database type I’m connecting to, and then my ‘modules’ request their queries be loaded. I have a directory structure of /queries/database – like

/queries/mysql
/queries/mssql
/queries/pgsql
/queries/sqlite
/queries/oracle

and so on that have different versions of the prepared queries in them which get added to my $this->queries array.

Net result, one PHP codebase targeting MULTIPLE databases.

That’s really what sold me on PDO over mysqli.