澳门银河网上官方赌场_老品牌值得信赖

ITKeyword,专注技术干货聚合推荐

注册 | 登录

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

itPublisher 分享于

2020腾讯云限时秒杀,爆款1核2G云服务器99元/年!(领取2860元代金券),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1062

2020阿里云最低价产品入口+领取代金券(老用户3折起),
入口地址https://www.aliyun.com/minisite/goods

推荐:SQL SELECT TOP N equivalent in ORACLE and MySQL

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:

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

php mysql sql apostrophe
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

 | 

6 Answers
6

解决方法

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

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


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.


相关阅读排行


相关内容推荐

最新文章

×

×

请激活账号

为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品,请激活账号。

您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱

如果您没有收到激活邮件,请注意检查垃圾箱。

澳门银河网上官方赌场