How to escape a sql query string with php to store it in a mysql table

Question

I am trying to store a sql query string in a MySQL field, and I am having problems correctly escaping the string.

If I insert the following string into the sql varchar field of the test table, using phpMyAdmin:

INSERT INTO `test` SET `test`.`sql`='{$_POST['sql']}'

and then export it using phpMyAdmin, it gives me the following sql query:

INSERT INTO  `test`.`test` (`sql`)
VALUES ('INSERT INTO `test` SET `test`.`sql`=''{$_POST[''sql'']}''');

If I want my own php script to do this, with what function do I escape:

INSERT INTO `test` SET `test`.`sql`='{$_POST['sql']}'

to make it look like:

'INSERT INTO `test` SET `test`.`sql`=''{$_POST[''sql'']}'''

I have a large number of sql queries I need to store for retrieval.

What is phpMyAdmin doing to the original string to create:

INSERT INTO  `test`.`test` (`sql`)
VALUES ('INSERT INTO `test` SET `test`.`sql`=''{$_POST[''sql'']}''');

What is the function xyz where:

$a = "INSERT INTO `test` SET `test`.`sql`='{$_POST['sql']}'";
$b = "INSERT INTO  `test`.`test` (`sql`)
VALUES ('INSERT INTO `test` SET `test`.`sql`=''{$_POST[''sql'']}''')";
$b = xyz($a);

What functions/libraries are you using for working with MySQL? — Jan Hančič
– Jan Hančič, Commented May 31, 2012 at 10:05
Also do not forget to add mysql_real_escape_string($_POST['sql']) - this handle a lot of escaping itself aside from the fact of security and injection. — TheBlackBenzKid
– TheBlackBenzKid, Commented May 31, 2012 at 10:12
$sql = "INSERT INTO test SET test.sql='" . mysql_real_escape_string($_POST['sql']) . "'"; $encoded2 = base64_encode ($sql); $decoded2 = base64_decode ($encoded2); echo $decoded2; // $decoded2 does not = $sql, I don't understand — user1421347
– user1421347, Commented May 31, 2012 at 10:30
Please stop writing new code with the ancient mysql_* functions. They are no longer maintained and community has begun the deprecation process . Instead you should learn about prepared statements and use either PDO or MySQLi. — Bono
– Bono, Commented May 31, 2012 at 11:33

Minras · Accepted Answer · 2012-05-31 10:12:19Z

4

You need mysql_real_escape_string():

$sql = "INSERT INTO `test` SET `test`.`sql`='" . mysql_real_escape_string($_POST['sql']) . "'";

answered May 31, 2012 at 10:12

Minras

4,3664 gold badges20 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

TheBlackBenzKid Over a year ago

You beat me to it.. my answer was placed in comments.

user1421347 Over a year ago

I have a large number of queries that I need to store for retrieval - I would prefer not to edit every query, just process each query as an entire string. And in any event, this does not return the original string (or am I missing something?)

Minras Over a year ago

@user1421347, the issue is that if you don't escape the data before creating a query, you can possibly get invalid query and in that case further escaping is useless. Of course you can parse the query inside xyz() and escape the extracted arguments, but as for me that is more complicated way.

Minras Over a year ago

@TheBlackBenzKid, sorry, I can only give you +1 :)

user1421347 Over a year ago

It's not clear to me that you are understanding my question - stackoverflow.com is able to save the entire string (INSERT INTO test SET test.sql='{$_POST['sql']}'), and reproduce here on this page - how do they do that? Perhaps they are not using PHP/MySQL, but it has to be possible.

|

Betaminos · Accepted Answer · 2012-05-31 12:42:33Z

-1

If you don't need to read the code inside the database (e.g. access it via phpMyAdmin and copy&paste the code) but only use PHP to do that, you could encode it using a base64 system.

For encoding (storing in the database)

base64_encode($your_query);

For decoding (retrieving from the database)

base64_decode($your_result);

Assuming that you have all the queries in a single file that does not contain anything different, you could try this approach

$queryString = file_get_contents("queries.sql"); //reads the file and stores it as a single String
$queries = explode(';', $queryString);
foreach ($queries as $query)
  mysql_query( ... $query ... ); //Store each query in the database

edited May 31, 2012 at 12:42

answered May 31, 2012 at 10:07

Betaminos

4825 silver badges13 bronze badges

13 Comments

TheBlackBenzKid Over a year ago

1970 something.. 1970 something - BIGGY.

user1421347 Over a year ago

Thanks - $str = "INSERT INTO test SET test.sql='{$_POST['sql']}'"; $encoded = base64_encode ($str); $decoded = base64_decode ($encoded); echo $decoded; - why i s this not the original string $str?

Betaminos Over a year ago

@user1421347: What have you used for sql? The $_POST['sql'] is evaluated and replaced. For me the solution seems to work, however I do not know what data you tried.

Betaminos Over a year ago

@TheBlackBenzKid: Could you please provide a suitable solution if you decide on downvoting working solutions (even if the style is quite old)?

user1421347 Over a year ago

I don't want to evaluate $_POST['sql'] - I want to store $str as a complete string and retrieve it as such

|

Collectives™ on Stack Overflow

How to escape a sql query string with php to store it in a mysql table

2 Answers 2

6 Comments

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related