Use the PHP addslashes function to fix SQL INSERT problems

PHP MySQL FAQ: Can you share an example of how to use the PHP addslashes function when creating a SQL INSERT statement?

When you're inserting records into a database table with plain PHP (as opposed to using a PHP framework), some of the fields you're inserting may have characters in them that will cause problems during the SQL INSERT process. Characters that will cause problems on database INSERT statements include the single quote, double quote, backslash, and NUL characters.

I ran into this problem yesterday when I wrote a SQL batch-insert program in PHP. This program reads thousands of records from a file, and inserts them into a MySQL database. The problem I ran into is that two of the fields had these offending characters in them, and my SQL INSERT statements kept blowing up. That's when I found what I needed: The PHP addslashes function.

The PHP addslashes function

Here's a nice description of the PHP addslashes function from the addslashes manual page:

Returns a string with backslashes before characters that need to be quoted in database queries etc. These characters are single quote ('), double quote ("), backslash (\) and NUL (the NULL byte).

An example use of addslashes() is when you're entering data into a database. For example, to insert the name O'reilly into a database, you will need to escape it. It's highly recommeneded to use DBMS specific escape function (e.g. mysqli_real_escape_string() for MySQL or pg_escape_string() for PostgreSQL), but if the DBMS you're using does't have an escape function and the DBMS uses \ to escape special chars, you can use this function. This would only be to get the data into the database, the extra \ will not be inserted. Having the PHP directive magic_quotes_sybase set to on will mean ' is instead escaped with another '.

A PHP addslashes function example

Here's a snippet of code that shows how I used the PHP addslashes function with my SQL INSERT statement. As you can see from the code below I first convert my strings ($uri and $title) with the addslashes function, and then run my SQL INSERT statement:

# titles and malformed URIs can have all sorts of characters in them; escape them
$uri = addslashes($uri);
$title = addslashes($title);

// do the mysql insert
$result = mysql_query(" INSERT INTO logfile_records (uri, title, date) " .
                      " VALUES ('$uri', '$title', '$the_date')");

// die if the insert doesn't succeed
if (!$result) {
  die('mysql query error: ' . mysql_error());
}

Before using this PHP addslashes function, my queries failed often, but after adding it in, they all worked great.

The PHP mysqli_real_escape_string function

Unfortunately I didn't read the addslashes function manual page until after my program was working, but since I'm using a MySQL database, it looks like it's preferable to use the PHP mysqli_real_escape_string function instead of the addslashes function, but for my simple batch-processing program, the PHP addslashes function has worked fine on hundreds of thousands of records, so I'm just going to stick with it for now. I'll try the mysqli_real_escape_string function on my next program and report the results back here.