blocks|key|307649|text|您应该使用mysql_real_escape_string()对每个字符串(在两个代码片段中)进行转义。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|307650|http://us3.php.net/mysql-real-escape-string|307651|您的两个查询行为不同的原因可能是因为您启用了magic_quotes_gpc+(您应该知道这不是一个好主意)。这意味着从$_GET、$_POST和$_COOKIES收集的字符串会自动转义(即"O'Brien"+->+"O\'Brien")。|307652|一旦您存储了数据，并随后再次检索它，您从数据库中返回的字符串将不会自动为您转义。你会得到"O'Brien"的。因此，您需要通过mysql_real_escape_string()传递它。|307653|entityMap|0|LINK|mutability|MUTABLE|url^0|5|Q|0|0|17|0|0|M|G|2N|N|0|18|9|1R|Q|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|V|8|@]|D|@$9|W|A|X|1|Y]]|E|$]]|$1|H|3|I|5|6|7|Z|8|@$9|10|A|11|B|C]|$9|12|A|13|B|C]]|D|@]|E|$]]|$1|J|3|K|5|6|7|14|8|@$9|15|A|16|B|C]|$9|17|A|18|B|C]]|D|@]|E|$]]|$1|L|3|-4|5|6|7|19|8|@]|D|@]|E|$]]]|M|$N|$5|O|P|Q|E|$R|G]]]]

You should be escaping each of these strings (in both snippets) with <code>mysql_real_escape_string()</code>.

<a href="http://us3.php.net/mysql-real-escape-string" rel="noreferrer">http://us3.php.net/mysql-real-escape-string</a>

The reason your two queries are behaving differently is likely because you have <code>magic_quotes_gpc</code> turned on (which you should know is a bad idea). This means that strings gathered from $_GET, $_POST and $_COOKIES are escaped for you (i.e., <code>"O'Brien" -&gt; "O\'Brien"</code>).

Once you store the data, and subsequently retrieve it again, the string you get back from the database will not be automatically escaped for you. You'll get back <code>"O'Brien"</code>. So, you will need to pass it through <code>mysql_real_escape_string()</code>.

blocks|key|307655|text|您应该这样做，以帮助您调试|type|unstyled|depth|inlineStyleRanges|entityRanges|data|307656|$sql+=+"insert+into+blah+values+('$myVar')";
echo+$sql;|code-block|syntax|javascript|307657|您可能会发现，在工作查询中，单引号是用反斜杠转义的。这可能是由PHP通过magic_quotes_gpc设置自动完成的，也可能是您在代码的其他部分自己完成的(addslashes和stripslashes可能是需要查找的函数)。|307658|请参阅|307659|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

You should do something like this to help you debug

<pre><code>$sql = "insert into blah values ('$myVar')";
echo $sql;
</code></pre>

You will probably find that the single quote is escaped with a backslash in the working query. This might have been done automatically by PHP via the magic_quotes_gpc setting, or maybe you did it yourself in some other part of the code (addslashes and stripslashes might be functions to look for).

See <a href="http://php.net/manual/en/security.magicquotes.php" rel="noreferrer">Magic Quotes</a>

blocks|key|309656|text|你有几个东西在你的弦上打架。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|309657|PHP+(mysql_real_escape_string())|offset|length|style|CODE|309658|potential缺乏正确的自动‘魔术引号’--检查你的PHP+setting|unordered-list-item|309659|
|309660|embedded字符串变量，这意味着你必须知道|309661|如何正确地找到变量MySQL+|309662|309663|309664|也有可能第一个查询的参数中没有出现单引号的值。毕竟，您的示例是一个恰当的名称，而且似乎只有第二个查询处理名称。|309665|entityMap^0|0|5|Q|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|@$D|Y|E|Z|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|10|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|11|8|@]|9|@]|A|$]]|$1|M|3|N|5|J|7|12|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|13|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|14|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|15|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|16|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|V|$]]

You have a couple of things fighting in your strings.

<ul>
<li>lack of correct MySQL quoting (<code>mysql_real_escape_string()</code>)</li>
<li>potential automatic 'magic quote' -- check your gpc_magic_quotes setting</li>
<li>embedded string variables, which means you have to know how PHP correctly finds variables</li>
</ul>

It's also possible that the single-quoted value is not present in the parameters to the first query. Your example is a proper name, after all, and only the second query seems to be dealing with names.

blocks|key|310690|text|我也有同样的问题，我是这样解决的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|310691|$text+=+str_replace("'",+"\'",+$YourContent);|code-block|syntax|javascript|310692|也许有一种更好的方法可以做到这一点，但它对我有效，对你也应该有效。|310693|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|$]]

I had the same problem and I solved it like this:

<pre><code>$text = str_replace("'", "\'", $YourContent);
</code></pre>

There is probably a better way to do this, but it worked for me and it should work for you too.

blocks|key|3262043|text|mysql_real_escape_string()或str_replace()函数将帮助您解决问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3262044|http://phptutorial.co.in/php-echo-print/|offset|length|3262045|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|14|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@$D|O|E|P|1|Q]]|A|$]]|$1|F|3|-4|5|6|7|R|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|C]]]]

mysql_real_escape_string() or str_replace() function will help you to solve your problem.

<a href="http://phptutorial.co.in/php-echo-print/" rel="nofollow"><a href="http://phptutorial.co.in/php-echo-print/" rel="nofollow">http://phptutorial.co.in/php-echo-print/</a></a>

blocks|key|3262056|text|只需在"mysql_real_escape_string(trim($val))“中传递变量(或数据)即可。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3262057|其中$val是困扰您的数据。|offset|length|style|CODE|3262058|entityMap^0|0|2|4|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

You should just pass the variable (or data) inside "mysql_real_escape_string(trim($val))" 

where <code>$val</code> is the data which is troubling you.

blocks|key|309725|text|您可以执行以下操作，从而同时转义PHP和MySQL。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|309726|<?
$text+=+'<a+href="javascript:window.open(\\\'http://www.google.com\\\');"></a>';
?>+|code-block|syntax|javascript|309727|这将把MySQL反映为|309728|<a+href="javascript:window.open('http://www.google.com');"></a>|309729|它是如何工作的？|offset|length|style|BOLD|309730|我们知道PHP和MySQL撇号都可以用反斜杠转义，然后用撇号转义。|309731|\'|309732|因为我们使用PHP插入到MySQL中，所以我们需要PHP仍然将反斜杠写到MySQL中，这样它也可以转义它。因此，我们使用PHP转义字符反斜杠-反斜杠和反斜杠-撇号来实现这一点。|309733|\\\'|309734|entityMap^0|0|0|0|0|0|8|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|12|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|14|8|@$M|15|N|16|O|P]]|9|@]|A|$]]|$1|Q|3|R|5|6|7|17|8|@]|9|@]|A|$]]|$1|S|3|T|5|D|7|18|8|@]|9|@]|A|$E|F]]|$1|U|3|V|5|6|7|19|8|@]|9|@]|A|$]]|$1|W|3|X|5|D|7|1A|8|@]|9|@]|A|$E|F]]|$1|Y|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]]|Z|$]]

You can do the following which escapes both PHP and MySQL.

<pre><code>&lt;?
$text = '&lt;a href="javascript:window.open(\\\'http://www.google.com\\\');"&gt;&lt;/a&gt;';
?&gt; 
</code></pre>

This will reflect MySQL as

<pre><code>&lt;a href="javascript:window.open('http://www.google.com');"&gt;&lt;/a&gt;
</code></pre>

How does it work? 

We know that both PHP and MySQL apostrophes can be escaped with backslash and then apostrophe.

<pre><code>\'
</code></pre>

Because we are using PHP to insert into MySQL, we need PHP to still write the backslash to MySQL so it too can escape it. 
So we use the PHP escape character of backslash-backslash together with backslash-apostrophe to achieve this. 

<pre><code>\\\'
</code></pre>

blocks|key|310715|text|对于任何在2015年找到这个解决方案并继续前进的人来说...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|310716|从PHP5.5.0开始，不推荐使用mysql_real_escape_string()函数。|offset|length|style|CODE|310717|请参阅：php.net|310718|警告|BOLD|310719|此扩展从PHP+5.5.0起已弃用，并将在将来删除。相反，应该使用MySQLi或PDO_MySQL扩展。有关更多信息，请参阅MySQL:选择应用程序接口指南和相关常见问题解答。此函数的替代方法包括：|310720|mysqli_real_escape_string()|310721|PDO::quote()|310722|entityMap|0|LINK|mutability|MUTABLE|url|http://us3.php.net/mysql-real-escape-string^0|0|H|Q|0|4|7|0|0|0|2|0|0|0|R|0|0|C|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@$D|12|E|13|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|14|8|@]|9|@$D|15|E|16|1|17]]|A|$]]|$1|J|3|K|5|6|7|18|8|@$D|19|E|1A|F|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|1B|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|1C|8|@$D|1D|E|1E|F|G]]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1F|8|@$D|1G|E|1H|F|G]]|9|@]|A|$]]|$1|S|3|-4|5|6|7|1I|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z]]]]

For anyone finding this solution in 2015 and moving forward...

The <code>mysql_real_escape_string()</code> function is deprecated as of PHP 5.5.0.

See: <a href="http://us3.php.net/mysql-real-escape-string" rel="noreferrer">php.net</a>

Warning

This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include:

<code>mysqli_real_escape_string()</code>

<code>PDO::quote()</code>

I have a perplexing issue that I can't seem to comprehend...

I have two SQL statements:

<ul>
<li>The first enters information from a form into the database.</li>
<li>The second takes data from the database entered above, sends an email, and then logs the details of the transaction</li>
</ul>

The problem is that it appears that a single quote is triggering a MySQL error on the second entry only! The first instance works without issue, but the second instance triggers the <code>mysql_error()</code>.

Does the data from a form get handled differently from the data captured in a form?

Query 1 - This works without issue (and without escaping the single quote)

<pre><code>$result = mysql_query("INSERT INTO job_log
(order_id, supplier_id, category_id, service_id, qty_ordered, customer_id, user_id, salesperson_ref, booking_ref, booking_name, address, suburb, postcode, state_id, region_id, email, phone, phone2, mobile, delivery_date, stock_taken, special_instructions, cost_price, cost_price_gst, sell_price, sell_price_gst, ext_sell_price, retail_customer, created, modified, log_status_id)
VALUES
('$order_id', '$supplier_id', '$category_id', '{$value['id']}', '{$value['qty']}', '$customer_id', '$user_id', '$salesperson_ref', '$booking_ref', '$booking_name', '$address', '$suburb', '$postcode', '$state_id', '$region_id', '$email', '$phone', '$phone2', '$mobile', STR_TO_DATE('$delivery_date', '%d/%m/%Y'), '$stock_taken', '$special_instructions', '$cost_price', '$cost_price_gst', '$sell_price', '$sell_price_gst', '$ext_sell_price', '$retail_customer', '".date('Y-m-d H:i:s', time())."', '".date('Y-m-d H:i:s', time())."', '1')");
</code></pre>

Query 2 - This fails when entering a name with a single quote (for example, O'Brien)

<pre><code>$query = mysql_query("INSERT INTO message_log
(order_id, timestamp, message_type, email_from, supplier_id, primary_contact, secondary_contact, subject, message_content, status)
VALUES
('$order_id', '".date('Y-m-d H:i:s', time())."', '$email', '$from', '$row-&gt;supplier_id', '$row-&gt;primary_email' ,'$row-&gt;secondary_email', '$subject', '$message_content', '1')");
</code></pre>

Escaping single quote in PHP when inserting into MySQL

数据库

MySQL

我有一个令人困惑的问题，我似乎不能理解...我有两条SQL语句：首先将表单中的信息输入到数据库中。第二个从上面输入的数据库中提取数据，发送电子邮件，然后记录transaction的详细信息问题是，单个引号似乎只在第二个条目上触发了MySQL错误！第一个实例可以正常工作，但第二个实例会触发mysql_error()。表单...

问插入到MySQL时在PHP中转义单引号
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问插入到MySQL时在PHP中转义单引号EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问插入到MySQL时在PHP中转义单引号
EN