Proper quotation in mysqli_query

Question

There some problem in my query.

$test = "Don't look at me";

mysqli_query("INSERT INTO testtable SET testfield = '".$test."' ");

Notice there is a single quote on the string. When I execute it, it returns an error like

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't look at me ...
If I remove the single quote in the string, it works fine. So how can I save the string into the database without removing the single quote?

`$test = "Don\'t look at me";` or use `aadslashes()`. – Sougata Bose Jun 29 '15 at 05:35 — Sougata Bose, Jun 29 '15 at 05:35
or use mysqli_real_escape_string function – habib ul haq Jun 29 '15 at 05:35 — habib ul haq, Jun 29 '15 at 05:35
use `mysqli_real_escape_string($con,$test)`; – Saty Jun 29 '15 at 05:36 — Saty, Jun 29 '15 at 05:36

score 0 · Answer 1 · answered Jun 29 '15 at 05:36

0

You need to escape them properly-

$test = "Don\'t look at me";

or use aadslashes()

"INSERT INTO testtable SET testfield = '" .addslashes($test). "'"

answered Jun 29 '15 at 05:36

Sougata Bose

31,517
8
49
87

score 0 · Accepted Answer · answered Jun 29 '15 at 05:38

0

You can use mysqli_real_escape_string(). It is secures and also prevent your query with sql injection. also your table name and column name in backtick

$test = "Don't look at me";

$val=mysqli_real_escape_string($con,$test); //here you can pass your connection variable

mysqli_query("INSERT INTO `testtable` SET `testfield` = '".$val."' ");

Read Documemt mysql_real_escape_string()

answered Jun 29 '15 at 05:38

Saty

22,443
7
33
51

will that makes the string to be save on the database be the same after it is being save? – Jun 29 '15 at 05:44
like on the code, the string is `Don't look at me` and on the database, the string will still be `Don't look at me` – Jun 29 '15 at 05:45
also read my comment for table name and column name and don't forget to pass connection variable in `mysqli_real_escape_string` – Saty Jun 29 '15 at 05:47
why is it that i need to put `backtick` on the table name and column? – Jun 29 '15 at 05:52

score 0 · Answer 3 · answered Jun 29 '15 at 05:38

0

Or you can use

mysqli_real_escape_string(connection,escapestring)

mysqli_real_escape_string() function escapes special characters in a string for use in an SQL statement.

answered Jun 29 '15 at 05:38

habib ul haq

824
6
13

score 0 · Answer 4 · answered Jun 29 '15 at 05:40

0

You should not try to escape values by yourself. Use prepared statements for that: http://php.net/manual/en/mysqli.prepare.php , http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

answered Jun 29 '15 at 05:40

pisamce

533
2
6

Proper quotation in mysqli_query

4 Answers4