Mysql + php with special characters like "(Apostrophe)" and "(quotation mark)

Question

For some time I struggled with a small problem. He was there for years, but it was just an annoying problem, not a serious one, and I just worked on it. But now I want to find out if anyone can help me. I did some google'ing, but did not have time.

If I make a form post from html textarea in a php file, for example:

<form action="http://action.com" method="post"> <textarea name="text"><a href="http://google.com">google site</a></textarea> </form> 

and of course there is a submit button, etc.

The value is a problem: <a href="http://google.com">google site</a> The value of the text field has both "(quote mark) and" (apostrophe).

To save this in mysql_database, I do this:

 $result = mysql_query("INSERT INTO `table` (`row1`) VALUES ('".$_POST['text']."') ") or die(mysql_error()); 

And now I get mysql error:

You have an error in the SQL syntax; check the manual that matches the version of your MySQL server for the correct syntax to use next to the site '' on line 1

Answer 1

Your sql line will be:

 INSERT INTO `table` (`row1`) VALUES ('google site') 

This is not a valid statement. As Nunn wrote, avoid the string, at least with mysql_real_escape_string: http://php.net/manual/en/function.mysql-real-escape-string.php

And read about SQL injection http://en.wikipedia.org/wiki/SQL_injection

Think a little: if someone writes this: $_POST['text'] with the value: ');delete from table;....

You can say goodbye to your data :)

Always filter / clean input!

EDIT: since PHP 5.5.0 mysql_real_escape_string and the mysql extension are deprecated. Use mysqli extension and mysqli :: escape_string instead

Answer 2

Always at least use mysql_real_escape_string when adding custom values ​​to the database. You should examine the binding options or mysqli so that your query becomes:

 INSERT INTO `table` (`row1`) VALUES (?) 

AND? will be replaced with the actual value after disinfection of the entrance.

In your case, use:

 $result = mysql_query("INSERT INTO `table` (`row1`) VALUES ('".mysql_real_escape_string($_POST['text'])."') ") or die(mysql_error()); 

Reading SQL Injection. It is worth doing it right as soon as possible!

Answer 3

Escape the line: D

http://php.net/manual/en/function.mysql-real-escape-string.php

Answer 4

you can use the addslashes () function. This is a Quote line with a slash. therefore, it will be very useful for you when you add any apostrophe to your area.

 $result = mysql_query("INSERT INTO `table` (`row1`) VALUES ('".addslashes($_POST['text'])."') ") or die(mysql_error()); 

Answer 5

instead of using the old mysql * functions, use PDO and write parameterized queries - http://php.net/pdo

Answer 6

I also struggled with characters when I was updating data in mysql.

But I finally came up with a better answer. Here:

 $lastname = "$_POST["lastname"]"; //lastname is : O'Brian, Bran'storm 

And when you are going to update your database, the system will not update it if you are not using MySQL REAL Escape String. Here:

 $lastname = mysql_real_escape_string($_POST["lastname"]); // This Works Always. 

Then your request will be updated.

 Example: mysql_query("UPDATE client SET lastname = '$lastname' where clientID = '%"); //This will update your data and provide you with security. 

For more information, please check MYSQL_REAL_ESCAPE_STRING

Hope this helps