How to deal with the apostrophe when writing to MySQL database

Question

I get this error:

You have an error in your SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use next to 's', 'portal', '', 'offer', 'MSNBC', 'News', '', 'sports', '', "MSN", "Money", "Games" on line 3

The only problem is that this error appears when inserting data that contains an apostrophe. I tried changing the data type from VARCHAR to TEXT , but the result is still the same.

I tried to put in addslashes()

How to fix it?

 $query=" INSERT INTO alltags (id,tag1,tag2,tag3,tag4,tag5,tag6,tag7,tag8,tag9,tag10,tag11,tag12,tag13,tag14,tag15,tag16,tag17,tag18,tag19,tag20,tag21,tag22,tag23,tag24,tag25,tag26,tag27,tag28,tag29,tag30) VALUES ('',mysql_real_escape_string($uniqkey[0]),mysql_real_escape_string($uniqkey[1]),mysql_real_escape_string($uniqkey[2]),mysql_real_escape_string($uniqkey[3]),mysql_real_escape_string($uniqkey[4]),mysql_real_escape_string($uniqkey[5]),mysql_real_escape_string($uniqkey[6]),mysql_real_escape_string($uniqkey[7]),mysql_real_escape_string($uniqkey[8]),mysql_real_escape_string($uniqkey[9]),mysql_real_escape_string($uniqkey[10]),mysql_real_escape_string($uniqkey[11]),mysql_real_escape_string($uniqkey[12]),mysql_real_escape_string($uniqkey[13]),mysql_real_escape_string($uniqkey[14]),mysql_real_escape_string($uniqkey[15]),mysql_real_escape_string($uniqkey[16]),mysql_real_escape_string($uniqkey[17]),mysql_real_escape_string($uniqkey[18]),mysql_real_escape_string($uniqkey[19]),mysql_real_escape_string($uniqkey[20]),mysql_real_escape_string($uniqkey[21]),mysql_real_escape_string($uniqkey[22]),mysql_real_escape_string($uniqkey[23]),mysql_real_escape_string($uniqkey[24]),mysql_real_escape_string($uniqkey[25]),mysql_real_escape_string($uniqkey[26]),mysql_real_escape_string($uniqkey[27]),mysql_real_escape_string($uniqkey[28]),mysql_real_escape_string($uniqkey[29])) "; mysql_query($query) or die(mysql_error()); 

I changed it to mysql_real_escape_string . Is this syntax correct? I get errors.

Answer 1

The process of encoding data that contains characters that MySQL can interpret is called "escaping." You must escape your lines with mysql_real_escape_string , which is a PHP function, not a MySQL function, which means you have to run it in PHP before passing your query to the database. You must screen any data coming into your program from an external source. Any data that is not escaped is a potential SQL injection .

You must hide your data before building your request. Alternatively, you can build your query programmatically using the PHP and range looping constructs:

 // Build tag fields $tags = 'tag' . implode(', tag', range(1,30)); // Escape each value in the uniqkey array $values = array_map('mysql_real_escape_string', $uniqkey); // Implode values with quotes and commas $values = "'" . implode("', '", $values) . "'"; $query = "INSERT INTO alltags (id, $tags) VALUES ('', $values)"; mysql_query($query) or die(mysql_error()); 

Answer 2

Using mysql_real_escape_string is a more secure character handling approach for inserting / updating SQL:

 INSERT INTO YOUR_TABLE VALUES (mysql_real_escape_string($var1), mysql_real_escape_string($var2)) 

In addition, I would change your columns from TEXT to VARCHAR - search, in addition to indexing, works much better.

Update for your update

Being that id is an auto_increment column, you can:

• leave it in the column list, so you do not need to specify a value in the VALUES clause:

   INSERT INTO alltags (tag1,tag2,tag3,tag4,tag5,tag6,tag7,tag8,tag9,tag10,tag11,tag12,tag13,tag14,tag15,tag16,tag17,tag18,tag19,tag20,tag21,tag22,tag23,tag24,tag25,tag26,tag27,tag28,tag29,tag30) VALUES (mysql_real_escape_string($uniqkey[0]),mysql_real_escape_string($uniqkey[1]),mysql_real_escape_string($uniqkey[2]),mysql_real_escape_string($uniqkey[3]),mysql_real_escape_string($uniqkey[4]),mysql_real_escape_string($uniqkey[5]),mysql_real_escape_string($uniqkey[6]),mysql_real_escape_string($uniqkey[7]),mysql_real_escape_string($uniqkey[8]),mysql_real_escape_string($uniqkey[9]),mysql_real_escape_string($uniqkey[10]),mysql_real_escape_string($uniqkey[11]),mysql_real_escape_string($uniqkey[12]),mysql_real_escape_string($uniqkey[13]),mysql_real_escape_string($uniqkey[14]),mysql_real_escape_string($uniqkey[15]),mysql_real_escape_string($uniqkey[16]),mysql_real_escape_string($uniqkey[17]),mysql_real_escape_string($uniqkey[18]),mysql_real_escape_string($uniqkey[19]),mysql_real_escape_string($uniqkey[20]),mysql_real_escape_string($uniqkey[21]),mysql_real_escape_string($uniqkey[22]),mysql_real_escape_string($uniqkey[23]),mysql_real_escape_string($uniqkey[24]),mysql_real_escape_string($uniqkey[25]),mysql_real_escape_string($uniqkey[26]),mysql_real_escape_string($uniqkey[27]),mysql_real_escape_string($uniqkey[28]),mysql_real_escape_string($uniqkey[29])) "; 

• include id in the column list, which requires the use of a value in its place in the VALUES clause:

  • NULL
  • DEFAULT

Here is an example of using NULL as an id placeholder:

 INSERT INTO alltags (id,tag1,tag2,tag3,tag4,tag5,tag6,tag7,tag8,tag9,tag10,tag11,tag12,tag13,tag14,tag15,tag16,tag17,tag18,tag19,tag20,tag21,tag22,tag23,tag24,tag25,tag26,tag27,tag28,tag29,tag30) VALUES (NULL,mysql_real_escape_string($uniqkey[0]),mysql_real_escape_string($uniqkey[1]),mysql_real_escape_string($uniqkey[2]),mysql_real_escape_string($uniqkey[3]),mysql_real_escape_string($uniqkey[4]),mysql_real_escape_string($uniqkey[5]),mysql_real_escape_string($uniqkey[6]),mysql_real_escape_string($uniqkey[7]),mysql_real_escape_string($uniqkey[8]),mysql_real_escape_string($uniqkey[9]),mysql_real_escape_string($uniqkey[10]),mysql_real_escape_string($uniqkey[11]),mysql_real_escape_string($uniqkey[12]),mysql_real_escape_string($uniqkey[13]),mysql_real_escape_string($uniqkey[14]),mysql_real_escape_string($uniqkey[15]),mysql_real_escape_string($uniqkey[16]),mysql_real_escape_string($uniqkey[17]),mysql_real_escape_string($uniqkey[18]),mysql_real_escape_string($uniqkey[19]),mysql_real_escape_string($uniqkey[20]),mysql_real_escape_string($uniqkey[21]),mysql_real_escape_string($uniqkey[22]),mysql_real_escape_string($uniqkey[23]),mysql_real_escape_string($uniqkey[24]),mysql_real_escape_string($uniqkey[25]),mysql_real_escape_string($uniqkey[26]),mysql_real_escape_string($uniqkey[27]),mysql_real_escape_string($uniqkey[28]),mysql_real_escape_string($uniqkey[29])) "; 

I really want to emphasize that you should not customize your columns.

Answer 3

Slight improvement in the answer of the maygar:

EDIT: meagar updated his post, so his answer is now better.

 $query = 'INSERT INTO alltags (id, '; // append tag1, tag2, etc. $query .= 'tag' . implode(', tag', range(1, 30)) . ") VALUES ('', "; // escape each value in the uniqkey array $escaped_tags = array_map('mysql_real_escape_string', $uniqkey); // implode values with quotes and commas, and add closing bracket $query .= "'" . implode("', '", $escaped_tags) . "')"; // actually query mysql_query($query) or die(mysql_error()); 

Answer 4

Please take a look at the Migars. This is the correct code.

If you want to use the erroneous mysql_query () function, you need to split the SQL string as follows:

 mysql_query( "INSERT INTO whateever (col1,col2,col3,col4) VALUES (" . mysql_real_escape_string($col1) . "," . mysql_real_escape_string($col2) . "," . mysql_real_escape_string($col3) . "," . mysql_real_escape_string($col4) . ")" ); 

Or, since you have an array, use the smart method method to escape immediately:

 $uniqkey = array_map("mysql_real_escape_string", $uniqkey); mysql_query("USE THE ESCAPED ARRAY THEN DIRECTLY ('$uniqkey[0]', '$uniqkey[1]', '$uniqkey[2]', '$uniqkey[3]', ...");