Export data to .sql format. How to run away?

Question

I am writing an export tool that converts json input to sql statements.

This tool (and should not) know about database connections, it should simply output .sql, which can be used with other tools, does the actual import.

Most functions related to mysqli → * and PDO depend on an open connection (for defining things like a character set). What a good way to do this?

Answer 1

The reason MySQL functions require a connection in order to avoid a string is because all mysql_real_escape_string() makes a call to MySQL - in the escaping function.

However, if you read the manual page for it , you will see that they list the characters that are escaped:

mysql_real_escape_string () calls the MySQL library function mysql_real_escape_string, which adds a backslash to the following characters: \ x00, \ n, \ r, \, ', "and \ x1a.

You do not want to use addslashes() , as this only eludes several characters and does not provide a safe solution. But you should be able to mysql_real_escape_string() escaping performed by mysql_real_escape_string() using a list of characters, with a simple call to strtr() or similar:

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

Answer 2

For escaping, the mysql_real_escape_string function is the usual choice for this task, except that it requires a connection. Another alternative would be addslashes .

I would look at the mysqldump file with the necessary parameters (character sets, drag and drop tables, etc.) and take it from there as a starting point.

Answer 3

@stefgosselin: A connection is required for mysql_real_escape_string.

I would go to the line with the least resistance. The tool uses a system call to execute mysqldumper> tmpfile

Answer 4

just a thought, is it possible for you to generate an application and sql, for example

 INSERT INTO table1 (id, name) VALUES (?, ?); 

and pass the parameter set for sql as an array

 $parms = array('value1', 'value2'); 

and then the part of your application that does the database work, escapes at that point

 function writeToDb($sql, $parms) { // do escaping here }