How to escape php mysqli query?

Question

I am passing data from AJAX to my PHP. I just run a for loop to make my query. Problem with my data is that it contains single quote.

I am using single quote to enclose my parameters in the query. Query is something like

   INSERT INTO myTable (column1.column2) VALUES('value1', 'value2'), 
   ('value'1', 'value2');

I want to escape like

   INSERT INTO myTable (column1.column2) VALUES('value1', 'value2'), 
   ('value\'1', 'value2');

I just tried mysqli_real_Escape_String. It returns something like

   INSERT INTO myTable (column1.column2) VALUES(\'value1\', \'value2\'), 
   (\'value\'1\', \'value2\');

So Query execution fails.

I don't think using htmlspeciachars is the right way for this.

Any suggestions?

I recommend you to use [PDO](http://php.net/manual/fr/book.pdo.php) to access DB in PHP. If you won't, you can use [mysql_escape_string](http://php.net/manual/fr/function.mysql-escape-string.php) — Xavier W., Oct 20 '15 at 16:02
Use prepared statements - both mysqli and PDO support them and it will make life soooo much easier once you get the hang of them — Professor Abronsius, Oct 20 '15 at 16:03
I don't think I can use parameters. Since number of rows and number of columns are dynamic. I am using mysqli_query only — Gibbs, Oct 20 '15 at 16:08

score 1 · Answer 1 · answered Oct 20 '15 at 16:51

You should definitely be using prepared statements. They're not that tricky.

However, if you're not going to make that jump then you just need to use mysqli_real_escape_string properly.

From the result you got, I'm guessing you wrapped the whole query in the mysqli_real_escape_string function. However you should just wrap the value in it.

i.e.

"INSERT INTO myTable (column1, column2) VALUES('value1', 'value2'), 
   ('" . mysql_real_escape_string("value'1") . "', 'value2')";

Thats a pretty contrived way of doing things. But the idea is: only wrap the value in mysqli_real_escape_string().

How to escape php mysqli query?

1 Answers1