解决Mysql + php with special characters like '(Apostrophe) and ＂ (Quotation mark)
SQL SELECT TOP N equivalent in ORACLE and MySQL Something I needed today... I wish this was standarized, but it is not, so here are some examples: SQL
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:
php mysql sql apostrophe
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
edited Mar 5 at 8:25 J3STER 281 1 2 17 asked Jan 11 '11 at 16:23 Daniel Rufus Kaldheim 68 1 1 6 2 You must escape the string properly or you're allowing SQL Injection. As a nice side effect, preventing SQL injection will solve your problem. – thirtydot Jan 11 '11 at 16:28 You can you mysql_real_escape_string() – bharath Jan 11 '11 at 16:28
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
edited Dec 7 '15 at 17:55 Adam Lindsay 196 1 8 answered Jan 11 '11 at 16:30 Peter Porfy 6,559 2 20 39 input filtering has nothing to do with SQL. And escaping must be done for the every data, not just input – Your Common Sense Jan 11 '11 at 16:37 Lol, why the downvote? We are talking about input here, thats why i wrote about input. – Peter Porfy Jan 11 '11 at 16:39 That is why there is a ... at the end, I wrote it only to show anybody can run any sql with the current code. The question wasnt 'When should I escape data?' as I see, but 'what is wrong...' – Peter Porfy Jan 11 '11 at 16:46 1 Thanks for this, you gave me a heads up with the delete for table. The actual example is not that important to me. It's where I can find future answers! thanks! – Daniel Rufus Kaldheim Jan 11 '11 at 16:49 10 @pinusnegra: Pay no attention to Col. Shrapnel. His parents used to lock him in the closet for hours on end. Just pat him on the head and say, "Thanks, Colonel." And then briskly walk away. – webbiedave Jan 11 '11 at 18:43 | show more comment
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!
answered Jan 11 '11 at 16:27 methodin 5,152 16 21
Escape the string :D
answered Jan 11 '11 at 16:26 Nanne 48.6k 13 83 122
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());
answered Sep 26 '12 at 5:24 Hemi 343 1 3 11 Thanks, it worked. – Mohit Feb 21 at 18:32
instead of using the old mysql* functions, use PDO and write parameterized queries - http://php.net/pdo
answered Jan 11 '11 at 16:27 Stephen 10.7k 3 20 22
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
answered Oct 9 '15 at 9:48 Micheal P. 25 8
protected by Community? Jul 29 '14 at 18:41
Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?
Not the answer you're looking for? Browse other questions tagged php mysql sql apostrophe or ask your own question.
- 1php5.4.14/5.3.24/5.2.17安装(In windows),配置Apache,MySQL,phpMyAdmin,zendOptimizer,xdebug
- 2PHP访问MySql数据库 初级篇
- 5CentOS6.5系统下安装Apache2.4+PHP5.6+Mysql5.5 (LAMP)