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

Question

I have been struggling with a small problem for a while. It's been there for years but it's just been an irritating problem and not a serious one, and I have just worked around it. But now I want to find out if anyone can help me. I have done some google'ing but no success.

If I do a form post from a html textarea in a php file like this:

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

and of course there is a submit button and so on.

The value is the problem: <a href="http://google.com">google's site</a> The value of the textarea have both "(Quotation mark) and '(Apostrophe).

To save this in a mysql_database I do this:

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

And now I get the mysql error:

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 's site'' at line 1

Answer 1

Your sql string will be:

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

Which is not a valid statement. As Nanne wrote, escape 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 bit: if someone posts this: $_POST['text'] with value: ');delete from table;....

Your can say good bye to your data :)

Always filter/escape input!

EDIT: As of PHP 5.5.0 mysql_real_escape_string and the mysql extension are deprecated. Please use mysqli extension and mysqli::escape_string function instead

Answer 2

Always at least use mysql_real_escape_string when adding user-provided values into the Database. You should look into binding parameters or mysqli so your query would become:

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

And ? would be replaced by the actual value after sanitizing the input.

In your case use:

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

Read up on SQL Injection. It's worth doing right ASAP!

Answer 3

you can use addslashes() function. It Quote string with slashes. so, it will be very useful to you when you are adding any apostrophe in your field.

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

Answer 4

Escape the string :D

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

Answer 5

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

Answer 6

I was also Struggling about characters when I was updating data in mysql.

But I finally came to a better answer, Here is:

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

And When you are going to update your database, the system will not update it unless you use the MySQL REAL Escape String. Here:

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

Then you query will update certainly.

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

Answer 7

Just use prepared statements and you wouldn't have to worry about escaping or sql injection.

$con = <"Your database connection">;
$input = "What's up?";
$stmt = $con->prepare("insert into `tablename` (`field`)values(?)");
$stmt->bind_param("s",$input);
$stmt->execute();

Answer 8

If you are using php version > 5.5.0 then you have to use like this

$con = new mysqli("localhost", "your_user_name", "your_password", "your_db_name");

if ($con->query("INSERT into myCity (Name) VALUES ('".$con->real_escape_string($city)."')")) {
    printf("%d Row inserted.\n", $con->affected_rows);
}