blocks|key|1337493|text|简单地说：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1337494|SELECT+'This+is+Ashok''s+Pen.';|offset|length|style|CODE|1337495|因此，在字符串中，将每个单引号替换为其中的两个引号。|1337496|或者：|1337497|SELECT+'This+is+Ashok\'s+Pen.'|1337498|转义=)|1337499|entityMap^0|0|0|V|0|0|0|0|U|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@$D|T|E|U|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|V|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|W|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|X|8|@$D|Y|E|Z|F|G]]|9|@]|A|$]]|$1|N|3|O|5|6|7|10|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|Q|$]]

Put quite simply:

<code>SELECT 'This is Ashok''s Pen.';</code>

So inside the string, replace each single quote with two of them.

Or:

<code>SELECT 'This is Ashok\'s Pen.'</code>

Escape it =)

blocks|key|1553238|text|请参阅我对"How+to+escape+characters+in+MySQL"的回答|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1553239|无论你使用什么库与MySQL对话，都会有一个内置的转义函数，例如，在PHP语言中，你可以使用mysqli_real_escape_string或PDO::quote|1553240|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/881194/how-to-escape-special-character-in-mysql/881208#881208|1|http://php.net/mysqli_real_escape_string|2|http://php.net/manual/en/pdo.quote.php^0|5|Z|0|0|1A|P|1|20|A|2|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@$A|S|B|T|1|U]]|C|$]]|$1|D|3|E|5|6|7|V|8|@]|9|@$A|W|B|X|1|Y]|$A|Z|B|10|1|11]]|C|$]]|$1|F|3|-4|5|6|7|12|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]|N|$5|I|J|K|C|$L|O]]|P|$5|I|J|K|C|$L|Q]]]]

See my answer to <a href="https://stackoverflow.com/questions/881194/how-to-escape-special-character-in-mysql/881208#881208">"How to escape characters in MySQL"</a>

Whatever library you are using to talk to MySQL will have an escaping function built in, e.g. in PHP you could use <a href="http://php.net/mysqli_real_escape_string" rel="nofollow noreferrer">mysqli_real_escape_string</a> or <a href="http://php.net/manual/en/pdo.quote.php" rel="nofollow noreferrer">PDO::quote</a>

blocks|key|1338066|text|使用以下代码：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1338067|<?php
++++$var+=+"This+is+Ashok's+Pen.";

++++mysql_real_escape_string($var);
?>|code-block|syntax|javascript|1338068|这将解决您的问题，因为数据库无法检测字符串的特殊字符。|1338069|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Use this code:

<pre><code>&lt;?php
 $var = "This is Ashok's Pen.";

 mysql_real_escape_string($var);
?&gt;
</code></pre>

This will solve your problem, because the database can't detect the special characters of a string.

blocks|key|1337881|text|如果使用预准备语句，驱动程序将处理任何转义。例如(Java)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1337882|Connection+conn+=+DriverManager.getConnection(driverUrl);
conn.setAutoCommit(false);
PreparedStatement+prepped+=+conn.prepareStatement("INSERT+INTO+tbl(fileinfo)+VALUES(?)");
String+line+=+null;
while+((line+=+br.readLine())+!=+null)+{
++++prepped.setString(1,+line);
++++prepped.executeQuery();
}
conn.commit();
conn.close();|code-block|syntax|javascript|1337883|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you use prepared statements, the driver will handle any escaping. For example (Java):

<pre><code>Connection conn = DriverManager.getConnection(driverUrl);
conn.setAutoCommit(false);
PreparedStatement prepped = conn.prepareStatement("INSERT INTO tbl(fileinfo) VALUES(?)");
String line = null;
while ((line = br.readLine()) != null) {
 prepped.setString(1, line);
 prepped.executeQuery();
}
conn.commit();
conn.close();
</code></pre>

blocks|key|1553347|text|您应该使用\字符对特殊字符进行转义。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1553348|This+is+Ashok's+Pen.|code-block|syntax|javascript|1553349|变成：|1553350|This+is+Ashok\'s+Pen.|1553351|entityMap^0|5|1|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|P|$]]

You should escape the special characters using the <code>\</code> character.

<pre><code>This is Ashok's Pen.
</code></pre>

Becomes:

<pre><code>This is Ashok\'s Pen.
</code></pre>

blocks|key|1553662|text|在PHP中，使用mysqli_real_escape_string。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1553663|PHP手册中的示例：|1553664|<?php
$link+=+mysqli_connect("localhost",+"my_user",+"my_password",+"world");

/*+check+connection+*/
if+(mysqli_connect_errno())+{
++++printf("Connect+failed:+%25s\n",+mysqli_connect_error());
++++exit();
}

mysqli_query($link,+"CREATE+TEMPORARY+TABLE+myCity+LIKE+City");

$city+=+"'s+Hertogenbosch";

/*+this+query+will+fail,+cause+we+didn't+escape+$city+*/
if+(!mysqli_query($link,+"INSERT+into+myCity+(Name)+VALUES+('$city')"))+{
++++printf("Error:+%25s\n",+mysqli_sqlstate($link));
}

$city+=+mysqli_real_escape_string($link,+$city);

/*+this+query+with+escaped+$city+will+work+*/
if+(mysqli_query($link,+"INSERT+into+myCity+(Name)+VALUES+('$city')"))+{
++++printf("%25d+Row+inserted.\n",+mysqli_affected_rows($link));
}

mysqli_close($link);
?>|code-block|syntax|javascript|1553665|entityMap|0|LINK|mutability|MUTABLE|url|https://www.php.net/manual/en/mysqli.real-escape-string.php^0|8|P|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|C|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

In PHP, use <a href="https://www.php.net/manual/en/mysqli.real-escape-string.php" rel="nofollow noreferrer">mysqli_real_escape_string</a>.

Example from the PHP Manual:

<pre><code>&lt;?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
 printf("Connect failed: %s\n", mysqli_connect_error());
 exit();
}

mysqli_query($link, "CREATE TEMPORARY TABLE myCity LIKE City");

$city = "'s Hertogenbosch";

/* this query will fail, cause we didn't escape $city */
if (!mysqli_query($link, "INSERT into myCity (Name) VALUES ('$city')")) {
 printf("Error: %s\n", mysqli_sqlstate($link));
}

$city = mysqli_real_escape_string($link, $city);

/* this query with escaped $city will work */
if (mysqli_query($link, "INSERT into myCity (Name) VALUES ('$city')")) {
 printf("%d Row inserted.\n", mysqli_affected_rows($link));
}

mysqli_close($link);
?&gt;
</code></pre>

blocks|key|1338160|text|你可以使用下面的代码，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1338161|<?php
+++++$var+=+"This+is+Ashok's+Pen.";
+++++addslashes($var);
?>|code-block|syntax|javascript|1338162|如果mysqli_real_escape_string()不起作用。|1338163|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You can use this code,

<pre><code>&lt;?php
 $var = "This is Ashok's Pen.";
 addslashes($var);
?&gt;
</code></pre>

if mysqli_real_escape_string() does not work.

blocks|key|1553726|text|如果您使用的是PHP，只需使用addslashes()函数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1553727|PHP+Manual+addslashes|offset|length|1553728|entityMap|0|LINK|mutability|MUTABLE|url|http://www.php.net/addslashes^0|0|0|L|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

If you are using PHP, just use the addslashes() function.

<a href="http://www.php.net/addslashes" rel="nofollow noreferrer">PHP Manual addslashes</a>

blocks|key|1553790|text|$var+=+mysqli_real_escape_string($conn,+$_POST['varfield']);|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1553791|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>$var = mysqli_real_escape_string($conn, $_POST['varfield']);
</code></pre>

blocks|key|1337813|text|对于编程访问，可以使用placeholders自动转义不安全字符。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1337814|例如，在Perl+DBI中，您可以使用：|1337815|my+$string+=+"This+is+Ashok's+pen";
$dbh->do("insert+into+my_table(my_string)+values(?)",undef,($string));+|code-block|syntax|javascript|1337816|entityMap|0|LINK|mutability|MUTABLE|url|http://web.archive.org/web/20051218150351/http://www.geekuprising.com/create_better_perl_and_sql_with_placeholders^0|B|C|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|C|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

For programmatic access, you can use <a href="http://web.archive.org/web/20051218150351/http://www.geekuprising.com/create_better_perl_and_sql_with_placeholders" rel="nofollow">placeholders</a> to automatically escape unsafe characters for you. 

In Perl DBI, for example, you can use:

<pre><code>my $string = "This is Ashok's pen";
$dbh-&gt;do("insert into my_table(my_string) values(?)",undef,($string)); 
</code></pre>

blocks|key|1338095|text|也许你可以看看MySQL手册中的function+QUOTE。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1338096|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_quote^0|P|5|P|5|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]]|D|@$9|Q|A|R|1|S]]|E|$]]|$1|F|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|G|$H|$5|I|J|K|E|$L|M]]]]

Maybe you could take a look at function <a href="http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_quote" rel="nofollow noreferrer"><code>QUOTE</code></a> in the MySQL manual.

blocks|key|1553820|text|我使用Delphi的方式：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1553821|TheString+to+"escape"：|1553822|TheString="+bla+bla+bla+'em+some+more+apo:S+'em+and+so+on+";|code-block|syntax|javascript|1553823|解决方案：|1553824|StringReplace(TheString,+#39,'\'%2B#39,+[rfReplaceAll,+rfIgnoreCase]);|1553825|结果：|1553826|TheString="+bla+bla+bla+\'em+some+more+apo:S+\'em+and+so+on+";|1553827|此函数将所有字符(39)替换为"\'“，允许您在MySQL中插入或更新文本字段而不会出现任何问题。|1553828|在所有编程语言中都可以找到类似的函数！|1553829|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|10|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|11|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|12|8|@]|9|@]|A|$G|H]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

The way I do, by using Delphi:

TheString to "escape":

<pre><code>TheString=" bla bla bla 'em some more apo:S 'em and so on ";
</code></pre>

Solution:

<pre><code>StringReplace(TheString, #39,'\'+#39, [rfReplaceAll, rfIgnoreCase]);
</code></pre>

Result:

<pre><code>TheString=" bla bla bla \'em some more apo:S \'em and so on ";
</code></pre>

This function will replace all Char(39) with "\'" allowing you to insert or update text fields in MySQL without any problem.

Similar functions are found in all programming languages!

blocks|key|1338200|text|可以使用addslahes()或mysql_real_escape_string()。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1338201|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Use either addslahes() or mysql_real_escape_string().

blocks|key|1553907|text|作为Python用户，我将引号替换为原始的"'"：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1553908|don_not_work_str+=+"This+is+Ashok's+Pen."
work_str+=+don_not_work_str.replace("'",+r"\'")|code-block|syntax|javascript|1553909|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

As a Python user I replace the quote with a raw &quot;'&quot;:
<pre><code>don_not_work_str = &quot;This is Ashok's Pen.&quot;
work_str = don_not_work_str.replace(&quot;'&quot;, r&quot;\'&quot;)
</code></pre>

How do I insert a value in MySQL that consist of single or double quotes. i.e

<pre><code>This is Ashok's Pen.
</code></pre>

The single quote will create problems. There might be other escape characters. 

How do you insert the data properly?

How to escape single quotes in MySQL

如何在MySQL中插入由单引号或双引号组成的值。i.eThis is Ashok's Pen.单引号会产生问题。可能还有其他转义字符。如何正确地插入数据？

问如何在MySQL中转义单引号
EN

回答 14

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySQL中转义单引号EN

回答 14

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySQL中转义单引号
EN