In PHP, how does PDO protect from SQL injections? How do prepared statements work?

Question

I understand the right way to protect a db from SQL injection is by using prepared statements. I would like to understand how prepared statements protect my db.

For starters, are prepared statements the same thing as "parameterised queries"?

As an example, I'm pasting below my code for the insertion of a new user in a user table. Is that secure? How does PDO work to make it secure? Does anything more needs to be done to secure the db from injection?

In 'Class_DB.php':

class DB {
 private $dbHost;
 private $dbName;
 private $dbUser;
 private $dbPassword;   
 function __construct($dbHost, $dbName, $dbUser, $dbPassword) {
  $this->dbHost=$dbHost;
  $this->dbName=$dbName;
  $this->dbUser=$dbUser;
  $this->dbPassword=$dbPassword;
 }
 function createConnexion() {
  return new PDO("mysql:host=$this->dbHost;dbName=$this->dbName", $this->dbUser, $this->dbPassword);
 }
}

In 'DAO_User.php':

require_once('Class_DB.php');

class DAO_User {
 private $dbInstance;
 function __construct($dbInstance){
  $this->dbInstance=$dbInstance;
 }
 function createUser($user){
  $dbConnection=$this->dbInstance->createConnexion();
  $query=$dbConnection->prepare("INSERT INTO users (userName, hashedPassword, userEmail) VALUES (?,?,?)");
  $query->bindValue(1, $user->userName);
  $query->bindValue(2, $user->hashedPassword);
  $query->bindValue(3, $user->userEmail);
  $query->execute();
 }
}

Thanks,

JDelage

Answer 1

Here's my somewhat limited view on the matter...

A prepared statement is compiled on the DB server with placeholders for variable input.

When you bind a parameter, you're telling the DB which values to use when you execute the query. It will then pass the value to the compiled statement.

The difference between binding parameters and plain old string injection is that with the former, the value is not interpolated but rather assigned. During execution, the DBMS hits a placeholder and requests the value to use. This way, there's no chance of quote characters or other nasties sneaking there way into the actual statement.

Answer 2

Without using prepared statements, you could not use placeholders (?) in your query.

Instead, you would need to include the value you want to insert directly into the SQL statement. This would result in a different SQL statement for every insert (bad for performance) and if you are not careful about what strings you put into the statement you may also end up with a statement that does something else then what you intended to (for example SQL injection could happen).

This situation is somewhat similar to having a Javascript function that does some fixed code with variable input parameters, versus pasting the input parameters into the Javascript source and then eval'ing it.

Answer 3

Ok, I found the answer to my question in this related question: http://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection

Thanks to Haim for pointing this Q to me.

In non technical terms, here is how prepared statements protect from injection:

When a query is sent to a data base, it's typically sent as a string. The db engine will try to parse the string and separate the data from the instructions, relying on quote marks and syntax. So if you send "SELECT * WHERE 'user submitted data' EQUALS 'table row name', the engine will be able to parse the instruction.

If you allow a user to enter what will be sent inside 'user submitted data', then they can include in this something like '..."OR IF 1=1 ERASE DATABASE'. The db engine will have trouble parsing this and will take the above as an instruction rather than a meaningless string.

The way PDO works is that it sends separately the instruction (prepare("INSERT INTO ...)) and the data. The data is sent separately, clearly understood as being data and data only. The db engine doesn't even try to analyze the content of the data string to see if it contains instructions, and any potentially damaging code snipet is not considered.