PHP Prepared Statement

[dani33l_87]

I have hard time to understand what is a PHP Prepared Statement? and how can barricades SQL

injection ?

 

On the internet I don`t found a good explanation?

[kicken]

They are not really a PHP Prepared statement, just a Prepared Statement.  Basically what they are is a way for you to separate the values you need to use in a SQL query from the query itself.  You can sort of compare them to a template like you'd use to separate your PHP from your HTML.  Within the query text you just input placeholder values using either ? or :name style parameters.  For example:

$sql = 'SELECT userId FROM users WHERE username=:user AND password=:pass';

 

Then within PHP you prepare that sql to generate a statement.  How to prepare and handle the statement depends on which DB access api you're using.  I'll assume PDO for the rest of this example.

        //$db is an instance of PDO
$stmt = $db->prepare($sql);

 

Once you have the statement object you then bind your desired values to the placeholders you put into the query:

$stmt->bindValue(':user', $_POST['username']);
$stmt->bindValue(':pass', $_POST['password']);

 

Finally you have to execute that statement which actually sends the SQL and the values to the server so it can run it and give you back results:

$stmt->execute();

 

Now, the reason why this is better and prevents SQL injection is because the values are always kept separate from the SQL text.  They are not combined into a single string at any point (exception is if you use emulated prepares w/o PDO which you should avoid if possible).

 

Gonna stretch a bit for an analogy here, can't think of a better one, but think of it like you were selling someone a piece of furniture.  The older put the values into the SQL method would be like you selling someone the furniture pre-built.  If you built it incorrectly then when they try and use the furniture it may break.  How badly it break's depends on how badly you put it together.  Going the prepared statements method on the other hand would be like instead selling them all the parts they need along with a nice detailed instruction booklet on how to put it together properly. They end up with a nice sturdy piece of furniture.

 

When the query is prepared it is sent the SQL text and parses it, figuring out how it wants to execute to the query and where it needs to use user-defined values.  When you execute the query then you're sending the SQL server the actual values you want it to use when executing the previously prepared query.  As it executes the query whenever it gets to the point where it needs a value it will look up the value it needs from the values sent to it.