Exporting data to a .sql format. How to escape?

Question

I'm writing an export tool that converts input json data to sql statements.

This tool is (and should not) be aware of database connections, it should just output a .sql that can be used with other tools do the actual import.

Most of the mysqli->* and PDO-related functions rely on an open connection (to determine things like the characterset). What's a good way to go about this?

Answer 1

The reason the MySQL functions require a connection in order to escape the string is that all mysql_real_escape_string() does is make a call to MySQL's built-in escaping function.

However, if you read the manual page for it, you'll see that they do list the characters which are escaped:

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

You don't want to use addslashes() since that only escapes a few characters, and would not provide a secure solution. But you should be able to re-implement the escaping done by mysql_real_escape_string() using the list of characters given, with a simple call to strtr() or similar:

$replacements = array("\x00"=>'\x00',
                      "\n"=>'\n',
                      "\r"=>'\r',
                      "\\"=>'\\\\',
                      "'"=>"\'",
                      '"'=>'\"',
                      "\x1a"=>'\x1a');
$escaped = strtr($unescaped,$replacements);