PC Repaire Windows Error

Using prepared statements in PHP

by | Mar 8, 2021

Using prepared statements in php

Whether you’re reading from, or writing to a database, using prepared statements are easy, convenient, and secure. So what are they?

About these examples

The examples here are all for PHP’s built-in database layer, PDO, but many other database layers also support prepared statements, for example, Doctrine and mysqli.

If you look at an HTML file, you’ll see that it’s carrying both the content and instructions on how to show it. Usually, when you write an SQL query, it’s the same. The problem with that is that if you’re not careful, you (or more annoyingly, someone else) can write content that gets mistaken for commands.

In HTML, that ends up with broken layouts or cross-site scripting attacks, but with databases, it can hand over all your data to attackers.

With HTML the only answer is to always escape your content so it can never be interpreted as HTML tags when you don’t want it to. You can do that with your SQL too, by using your database’s escaping functions (like mysql_real_escape_string()), but there’s a better way, which is to use a prepared statement. With this method, your command and the content are sent
along separate channels to the database, which means you never need to worry about things like SQL injection attacks.

Prepared statements are also faster if you’re running the same query often since your database won’t need to interpret the command every time it’s sent.

Look at the SQL statement in the example below. You can see that instead of putting  $title we want directly into the query, we put a placeholder instead, called :title, and then we “bind” the value we want to the placeholder. PDO then passes the SQL and the data to the database separately.

Values vs Params

Notice that in the examples, I’ve used bindValue(). If you’ve looked at the PDO manual, you may have wondered what the difference is between bindValue()and bindParam(). The answer is that bindParam() connects the variable by
reference
instead of just taking the value from the variable. This means if you change the variable, you change the SQL statement. It’s pretty useful in a foreach loop if you’re adding a lot of rows to your database, but it can also cause some pretty confusing errors if you don’t realise what it’s doing. My personal recommendation is to avoid surprises and always use bindValue().

WHERE … IN

Sadly MySQL doesn’t support arrays in prepared statements, for example:

SELECT * FROM my_table WHERE id IN (1, 5, 7);

In this instance, you’ll have to escape the values manually using PDO’s quote() method.

In the example above, we use array_map() to run the same method on every item in the input array, and return the result as a new array. The method is the quote() method of the PDO database object, which does the appropriate form of
backslashing for the current database connection. It’s really important that you use quote() instead of addslashes() because not all databases use quotes and backslashes the same way (or at all).

That’s pretty much all you need to know, but if you want to see more examples or find out more about prepared statements, have a look at the PHP manual page on the subject.

Related Articles

How to Unbrick Your A95X MAX TV Box

How to Unbrick Your A95X MAX TV Box

The Story : After Installing a SuperSu in my A95X MAX Tv Box, the box didn't want to reboot, then i did manage to flash a custom rom using the SD card method, but then i didn't know that this will break my Box, doing so my box was dead no boot logo no usb connection...

Thou shalt always(ish) use UTF-8 in PHP

Thou shalt always(ish) use UTF-8 in PHP

Character sets are a mystery to many native-English coders, and if you're not aware of them then you'll occasionally find that things break, or go weird. Unicode was designed to replace all existing character sets with a single universal one. This article explains how...

Keep your code clean and simple

Keep your code clean and simple

“Keep it simple, stupid” Always write code that you'll be able to understand in six months time when you've moved on to bigger and better things. If you have to be clever, leave yourself clues. It's less likely to break this week, and when it breaks in 6 months time,...