Backing up MySQL DB... Proper way to escape strings?

Question

So I am backing up my DB with the following code:

$rows = $this->_getDb()->fetchAll("SELECT * FROM $table");

foreach ($rows AS $row)
{
    foreach ($row AS &$value)
    {
        if (!is_numeric($value))
        {
            $value = "'".addcslashes($value, "'\\")."'";
            $value = str_replace("\n", '\n', $value);
        }
    }

    if (!$count)
    {
        $output .= "INSERT INTO `$table` (" .implode(', ', $columns). ") VALUES";
    }

    $count++;
    $output .= "\n(" .implode(', ', $row). "),";

    if ($count >= $limit)
    {
        $count = 0;
        $output = preg_replace('#,$#', ";\n\n", $output);
    }
}

$output = preg_replace('#,$#', ";\n\n", $output);

This seems to be working great... But I'm comparing my output to what I get from a PHPMyAdmin and I'm noticing some slight differences. With my code, because of how I am using addcslashes, if a string contains a ' it will escape it with \'. However, in the output from PHPMyAdmin, it will instead replace a single ' with two single quotes '' in a row.

Is there really a difference? Please look at the way I am escaping non-numeric fields and tell me if there is a better way.

score 2 · Accepted Answer · edited May 23 '17 at 11:56

The method of two single-quotes in a row '' is more standard SQL, so it is more portable.

Even MySQL supports a SQL_MODE called NO_BACKSLASH_ESCAPES that would make your backslash-treated strings invalid if you try to import the backup to a server with that mode enabled.

But I have to add comments to make it clear that your method of backing up your database is full of other problems.

You don't handle NULLs:
```
if (!is_numeric($value))
```
This does not handle column names that need to be delimited because they may conflict with SQL reserved words:
```
$output .= "INSERT INTO `$table` (" .implode(', ', $columns). ") VALUES";
```
If you have millions of rows in any of your tabes, trying to store the entire content of that table in the form of a series of INSERT statements in a single string will exceed PHP's max memory limit:
```
$output .= "\n(" .implode(', ', $row). "),";
```
You only show part of your script, but it may also not properly handle binary strings, character sets, triggers, stored routines, views, table options, etc.

You are really reinventing the wheel.

I've seen past questions where people attempt to do what you're doing, but it's difficult to get right:

It will be a much better use of your time to just use shell_exec() from PHP to call mysqldump or mydumper.

Backing up MySQL DB... Proper way to escape strings?

1 Answers1