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

When in doubt, escape it all. Can't be too safe.

Alright, alright. I get it

ALWAYS ESCAPE

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

When to escape? As soon as your site goes public.

Answer 4

If the data is a string, it must always be escaped.

However, it's better to use parameters instead.

Answer 5

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.

Answer 6

You escape a MySQL query string when any of the string is made up of user input, for example:

in PHP: $username = ;//VALUE FROM USER INPUT

then your query string is: "INSERT INTO table ('username') VALUES (".$username.")"

You would have to escape this mySQL query due to the fact the $username variable could potentionally have malicous code inserted by the client to be injected into your database.

Answer 7

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 8

If you are generating SQL yourself rather than using something like PDO, then you must always escape strings.

Escaping strings is a basic requirement of the SQL language. It's what allows you to use characters like apostrophes or backslashes in a string without everything going bad. There is no situation at all in which escaping strings is not required.

Even non-strings will have to be filtered to ensure that they are, indeed, non-strings.

If you are learning, please seriously consider learning something like PDO as many others have said, rather than escaping your own strings.