How to know when escape is necessary for MySQL

Question

I'm in the process of building a site with CodeIgniter. This is the 1st site that I've built myself that interacts with a database. I'm using MySQL for this project. How can I tell if data needs to be escaped before saving it to the database?

Answer 1

I would advice you to accustom yourself to use prepared statements. Especially since you are new to working with databases. The sooner you start using these, the easier it becomes a second nature.

I, for instance, didn't know about prepared statements when I started with databases. And I experienced my own stubborness when I came in touch with them. Because I had accustomed myself to another way of doing things already. Now, this might not be a character trade of yourself, but it doesn't hurt to start as soon as possible with it either way.

Prepared statements allow you to use placeholders in queries. These placeholders can then be substituted with actual values by binding them to the placeholders. This process of binding, automatically escapes the values.

Here's a (simple) PDO example:

$db = new PDO( /* some database parameters */ );
$statement = $db->prepare( 'INSERT INTO table VALUES( :username, :password )' );
$statement->bindValue( ':username', $dirtyUsername );
$statement->bindValue( ':password', $dirtyPassword );
$result = $statement->execute();
// result checking ommited for brevity

There's lot's more possibilities with PDO and prepared statements. For instance you can easily reuse the prepared statement in a loop, as such:

$statement = $db->prepare( 'INSERT INTO table VALUES( :username, :password )' );
foreach( $users as $dirtyUser )
{
    $statement->bindValue( ':username', $dirtyUser->username );
    $statement->bindValue( ':password', $dirtyUser->password );
    $result = $statement->execute();
    // result checking ommited for brevity
}

Or pass the placeholder bindings to the execute method, like so:

$statement = $db->prepare( 'INSERT INTO table VALUES( :username, :password )' );
$result = $statement->execute( array( 
                                   ':username' => $dirtyUsername, 
                                   ':password' => $dirtyPassword
                             ) );
// result checking ommited for brevity

... etc., etc.

Answer 2

Don't worry about escaping yourself (you WILL screw it up). Use a DB layer where you prepare the statement first, and then add data to it.

In PHP you should use PDO. You write

SELECT * FROM table WHERE key = :key AND value = :value

and then add the data in by calling functions.

Answer 3

If you're using the database class with query bindings, you don't have to do any manual escaping:

The secondary benefit of using binds is that the values are automatically escaped, producing safer queries. You don't have to remember to manually escape data; the engine does it automatically for you.