blocks|key|282025|text|query运行标准的SQL语句，并要求您正确地转义所有数据，以避免SQL注入和其他问题。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|282026|execute运行预准备语句，该语句允许您绑定参数以避免对参数进行转义或引用。如果您重复多次查询，execute的性能也会更好。预准备语句示例：|282027|$sth+=+$dbh->prepare('SELECT+name,+colour,+calories+FROM+fruit
++++WHERE+calories+<+:calories+AND+colour+=+:colour');
$sth->bindParam(':calories',+$calories);
$sth->bindParam(':colour',+$colour);
$sth->execute();
//+$calories+or+$color+do+not+need+to+be+escaped+or+quoted+since+the
//++++data+is+separated+from+the+query|code-block|syntax|javascript|282028|最佳实践是坚持使用预准备语句和+execute+，以提高安全性。|BOLD|282029|另请参阅：Are+PDO+prepared+statements+sufficient+to+prevent+SQL+injection?|282030|entityMap|0|LINK|mutability|MUTABLE|url|http://php.net/manual/en/pdo.query.php|1|http://php.net/manual/en/pdostatement.execute.php|2|https://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection^0|0|5|0|5|0|0|0|7|1D|7|0|7|1|0|0|0|F|G|7|G|7|0|5|1S|2|0^^$0|@$1|2|3|4|5|6|7|13|8|@$9|14|A|15|B|C]]|D|@$9|16|A|17|1|18]]|E|$]]|$1|F|3|G|5|6|7|19|8|@$9|1A|A|1B|B|C]|$9|1C|A|1D|B|C]]|D|@$9|1E|A|1F|1|1G]]|E|$]]|$1|H|3|I|5|J|7|1H|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1I|8|@$9|1J|A|1K|B|O]|$9|1L|A|1M|B|O]|$9|1N|A|1O|B|C]]|D|@]|E|$]]|$1|P|3|Q|5|6|7|1P|8|@]|D|@$9|1Q|A|1R|1|1S]]|E|$]]|$1|R|3|-4|5|6|7|1T|8|@]|D|@]|E|$]]]|S|$T|$5|U|V|W|E|$X|Y]]|Z|$5|U|V|W|E|$X|10]]|11|$5|U|V|W|E|$X|12]]]]

<a href="http://php.net/manual/en/pdo.query.php" rel="noreferrer"><code>query</code></a> runs a standard SQL statement and requires you to properly escape all data to avoid SQL Injections and other issues.

<a href="http://php.net/manual/en/pdostatement.execute.php" rel="noreferrer"><code>execute</code></a> runs a prepared statement which allows you to bind parameters to avoid the need to escape or quote the parameters. <code>execute</code> will also perform better if you are repeating a query multiple times. Example of prepared statements:

<pre><code>$sth = $dbh-&gt;prepare('SELECT name, colour, calories FROM fruit
 WHERE calories &lt; :calories AND colour = :colour');
$sth-&gt;bindParam(':calories', $calories);
$sth-&gt;bindParam(':colour', $colour);
$sth-&gt;execute();
// $calories or $color do not need to be escaped or quoted since the
// data is separated from the query
</code></pre>

Best practice is to stick with prepared statements and <code>execute</code> for increased security.

See also: <a href="https://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection">Are PDO prepared statements sufficient to prevent SQL injection?</a>

blocks|key|285421|text|不，它们是不一样的。除了它在客户端提供的转义之外，预准备语句在服务器端编译一次，然后可以在每次执行时传递不同的参数。这意味着您可以执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|285422|$sth+=+$db->prepare("SELECT+*+FROM+table+WHERE+foo+=+?");
$sth->execute(array(1));
$results+=+$sth->fetchAll(PDO::FETCH_ASSOC);

$sth->execute(array(2));
$results+=+$sth->fetchAll(PDO::FETCH_ASSOC);|code-block|syntax|javascript|285423|它们通常会给您带来性能改进，尽管在小范围内不是很明显。Read+more+on+prepared+statements+(MySQL+version)。|offset|length|285424|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html^0|0|0|R|1C|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

No, they're not the same. Aside from the escaping on the client-side that it provides, a prepared statement is compiled on the server-side once, and then can be passed different parameters at each execution. Which means you can do:

<pre><code>$sth = $db-&gt;prepare("SELECT * FROM table WHERE foo = ?");
$sth-&gt;execute(array(1));
$results = $sth-&gt;fetchAll(PDO::FETCH_ASSOC);

$sth-&gt;execute(array(2));
$results = $sth-&gt;fetchAll(PDO::FETCH_ASSOC);
</code></pre>

They generally will give you a performance improvement, although not noticeable on a small scale. <a href="https://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html" rel="noreferrer">Read more on prepared statements (MySQL version)</a>.

blocks|key|2997746|text|Gilean's+answer很棒，但我只想补充一点，有时最佳实践很少有例外，您可能希望以两种方式测试您的环境，看看哪种方式工作得最好。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2997747|有一次，我发现query对我来说工作得更快，因为我从运行PHP7的Ubuntu+Linux机器批量传输可信数据，而Microsoft+ODBC+driver+for+MS+SQL+Server的支持很差。|style|CODE|2997748|我提出这个问题是因为我有一个长时间运行的ETL脚本，我试图压缩它以提高速度。在我看来，query可能比prepare+&+execute更快，因为它只调用一个函数，而不是两个。参数绑定操作提供了很好的保护，但它可能很昂贵，如果不必要，还可以避免。|2997749|给出了几个罕见的条件|BOLD|2997750|如果你因为it's+not+supported+by+the+Microsoft+ODBC+driver.|2997751|If而不能重用一个准备好的语句，你不需要担心清理输入，简单的转义是可以接受的。|unordered-list-item|2997752|。这可能是因为Microsoft驱动程序不支持binding+certain+datatypes+isn't+supported+by+the+Microsoft+ODBC+driver.|ordered-list-item|2997753|PDO::lastInsertId。|2997754|2997755|2997756|这是我用来测试我的环境的一种方法，希望你能在你的环境中复制它或更好的东西：|2997757|首先，我在Microsoft+SQL+Server中创建了一个基本表|2997758|CREATE+TABLE+performancetest+(
++++sid+INT+IDENTITY+PRIMARY+KEY,
++++id+INT,
++++val+VARCHAR(100)
);|code-block|syntax|javascript|2997759|现在是性能指标的基本计时测试。|2997760|$logs+=+[];

$test+=+function+(String+$type,+Int+$count+=+3000)+use+($pdo,+&$logs)+{
++++$start+=+microtime(true);
++++$i+=+0;
++++while+($i+<+$count)+{
++++++++$sql+=+"INSERT+INTO+performancetest+(id,+val)+OUTPUT+INSERTED.sid+VALUES+($i,'value+$i')";
++++++++if+($type+===+'query')+{
++++++++++++$smt+=+$pdo->query($sql);
++++++++}+else+{
++++++++++++$smt+=+$pdo->prepare($sql);
++++++++++++$smt+->execute();
++++++++}
++++++++$sid+=+$smt->fetch(PDO::FETCH_ASSOC)['sid'];
++++++++$i%2B%2B;
++++}
++++$total+=+(microtime(true)+-+$start);
++++$logs[$type]+[]=+$total;
++++echo+"$total+$type\n";
};

$trials+=+15;
$i+=+0;
while+($i+<+$trials)+{
++++if+(random_int(0,1)+===+0)+{
++++++++$test('query');
++++}+else+{
++++++++$test('prepare');
++++}
++++$i%2B%2B;
}

foreach+($logs+as+$type+=>+$log)+{
++++$total+=+0;
++++foreach+($log+as+$record)+{
++++++++$total+%2B=+$record;
++++}
++++$count+=+count($log);
++++echo+"($count)+$type+Average:+".$total/$count.PHP_EOL;
}|2997761|我已经在我的特定环境中尝试了多个不同的试验和计数，并且始终比prepare/execute使用query快20-30%25的结果|2997762|2997763|+5.8128969669342准备|blockquote|2997764|2997765|5.8688418865204准备|2997766|4.2948560714722查询|2997767|4.9533629417419查询|2997768|5.9051351547241准备|2997769|4.332102060318查询|2997770|5.9672858715057准备|2997771|5.0667371749878查询|2997772|3.8260300159454查询|2997773|4.0791549682617查询|2997774|4.3775160312653查询|2997775|3.6910600662231查询|2997776|5.2708210945129准备|2997777|6.2671611309052准备|2997778|7.3791449069977准备|2997779|(7)准备平均值:+6.0673267160143|2997780|(8)平均查询次数:+4.3276024162769+|2997781|我很想看看这个测试在其他环境中的比较情况，比如MySQL。|2997782|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/4700740/4233593|1|https://www.microsoft.com/en-us/download/details.aspx?id=50419|2|https://en.wikipedia.org/wiki/Extract,_transform,_load|3|https://stackoverflow.com/q/39088156/4233593|4|https://stackoverflow.com/q/39044034/4233593|5|http://php.net/manual/en/pdo.lastinsertid.php^0|0|F|0|0|7|5|1L|13|1|0|17|5|1F|7|1P|7|K|3|2|0|0|A|0|5|1B|3|0|0|N|1Y|4|0|0|H|0|H|5|0|0|0|0|0|0|0|0|U|7|12|7|1B|5|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|2Y|8|@]|9|@$A|2Z|B|30|1|31]]|C|$]]|$1|D|3|E|5|6|7|32|8|@$A|33|B|34|F|G]]|9|@$A|35|B|36|1|37]]|C|$]]|$1|H|3|I|5|6|7|38|8|@$A|39|B|3A|F|G]|$A|3B|B|3C|F|G]|$A|3D|B|3E|F|G]]|9|@$A|3F|B|3G|1|3H]]|C|$]]|$1|J|3|K|5|6|7|3I|8|@$A|3J|B|3K|F|L]]|9|@]|C|$]]|$1|M|3|N|5|6|7|3L|8|@]|9|@$A|3M|B|3N|1|3O]]|C|$]]|$1|O|3|P|5|Q|7|3P|8|@]|9|@]|C|$]]|$1|R|3|S|5|T|7|3Q|8|@]|9|@$A|3R|B|3S|1|3T]]|C|$]]|$1|U|3|V|5|T|7|3U|8|@$A|3V|B|3W|F|G]]|9|@$A|3X|B|3Y|1|3Z]]|C|$]]|$1|W|3|-4|5|6|7|40|8|@]|9|@]|C|$]]|$1|X|3|-4|5|6|7|41|8|@]|9|@]|C|$]]|$1|Y|3|Z|5|6|7|42|8|@]|9|@]|C|$]]|$1|10|3|11|5|6|7|43|8|@]|9|@]|C|$]]|$1|12|3|13|5|14|7|44|8|@]|9|@]|C|$15|16]]|$1|17|3|18|5|6|7|45|8|@]|9|@]|C|$]]|$1|19|3|1A|5|14|7|46|8|@]|9|@]|C|$15|16]]|$1|1B|3|1C|5|6|7|47|8|@$A|48|B|49|F|G]|$A|4A|B|4B|F|G]|$A|4C|B|4D|F|G]]|9|@]|C|$]]|$1|1D|3|-4|5|6|7|4E|8|@]|9|@]|C|$]]|$1|1E|3|1F|5|1G|7|4F|8|@]|9|@]|C|$]]|$1|1H|3|-4|5|6|7|4G|8|@]|9|@]|C|$]]|$1|1I|3|1J|5|6|7|4H|8|@]|9|@]|C|$]]|$1|1K|3|1L|5|6|7|4I|8|@]|9|@]|C|$]]|$1|1M|3|1N|5|6|7|4J|8|@]|9|@]|C|$]]|$1|1O|3|1P|5|6|7|4K|8|@]|9|@]|C|$]]|$1|1Q|3|1R|5|6|7|4L|8|@]|9|@]|C|$]]|$1|1S|3|1T|5|6|7|4M|8|@]|9|@]|C|$]]|$1|1U|3|1V|5|6|7|4N|8|@]|9|@]|C|$]]|$1|1W|3|1X|5|6|7|4O|8|@]|9|@]|C|$]]|$1|1Y|3|1Z|5|6|7|4P|8|@]|9|@]|C|$]]|$1|20|3|21|5|6|7|4Q|8|@]|9|@]|C|$]]|$1|22|3|23|5|6|7|4R|8|@]|9|@]|C|$]]|$1|24|3|25|5|6|7|4S|8|@]|9|@]|C|$]]|$1|26|3|27|5|6|7|4T|8|@]|9|@]|C|$]]|$1|28|3|29|5|6|7|4U|8|@]|9|@]|C|$]]|$1|2A|3|2B|5|6|7|4V|8|@]|9|@]|C|$]]|$1|2C|3|2D|5|6|7|4W|8|@]|9|@]|C|$]]|$1|2E|3|2F|5|6|7|4X|8|@]|9|@]|C|$]]|$1|2G|3|-4|5|6|7|4Y|8|@]|9|@]|C|$]]]|2H|$2I|$5|2J|2K|2L|C|$2M|2N]]|2O|$5|2J|2K|2L|C|$2M|2P]]|2Q|$5|2J|2K|2L|C|$2M|2R]]|2S|$5|2J|2K|2L|C|$2M|2T]]|2U|$5|2J|2K|2L|C|$2M|2V]]|2W|$5|2J|2K|2L|C|$2M|2X]]]]

<a href="https://stackoverflow.com/a/4700740/4233593">Gilean's answer</a> is great, but I just wanted to add that sometimes there are rare exceptions to best practices, and you might want to test your environment both ways to see what will work best.

In one case, I found that <code>query</code> worked faster for my purposes because I was bulk transferring trusted data from an Ubuntu Linux box running PHP7 with the poorly supported <a href="https://www.microsoft.com/en-us/download/details.aspx?id=50419" rel="nofollow noreferrer">Microsoft ODBC driver for MS SQL Server</a>.

I arrived at this question because I had a long running script for an <a href="https://en.wikipedia.org/wiki/Extract,_transform,_load" rel="nofollow noreferrer">ETL</a> that I was trying to squeeze for speed. It seemed intuitive to me that <code>query</code> could be faster than <code>prepare</code> &amp; <code>execute</code> because it was calling only one function instead of two. The parameter binding operation provides excellent protection, but it might be expensive and possibly avoided if unnecessary.

Given a couple rare conditions:

<ol>
<li>If you can't reuse a prepared statement because <a href="https://stackoverflow.com/q/39088156/4233593">it's not supported by the Microsoft ODBC driver</a>.</li>
<li>If you're not worried about sanitizing input and simple escaping is acceptable. This may be the case because <a href="https://stackoverflow.com/q/39044034/4233593">binding certain datatypes isn't supported by the Microsoft ODBC driver</a>.</li>
<li><a href="http://php.net/manual/en/pdo.lastinsertid.php" rel="nofollow noreferrer"><code>PDO::lastInsertId</code></a> is not supported by the Microsoft ODBC driver.</li>
</ol>

Here's a method I used to test my environment, and hopefully you can replicate it or something better in yours:

To start, I've created a basic table in Microsoft SQL Server

<pre class="lang-sql prettyprint-override"><code>CREATE TABLE performancetest (
 sid INT IDENTITY PRIMARY KEY,
 id INT,
 val VARCHAR(100)
);
</code></pre>

And now a basic timed test for performance metrics.

<pre><code>$logs = [];

$test = function (String $type, Int $count = 3000) use ($pdo, &amp;$logs) {
 $start = microtime(true);
 $i = 0;
 while ($i &lt; $count) {
 $sql = "INSERT INTO performancetest (id, val) OUTPUT INSERTED.sid VALUES ($i,'value $i')";
 if ($type === 'query') {
 $smt = $pdo-&gt;query($sql);
 } else {
 $smt = $pdo-&gt;prepare($sql);
 $smt -&gt;execute();
 }
 $sid = $smt-&gt;fetch(PDO::FETCH_ASSOC)['sid'];
 $i++;
 }
 $total = (microtime(true) - $start);
 $logs[$type] []= $total;
 echo "$total $type\n";
};

$trials = 15;
$i = 0;
while ($i &lt; $trials) {
 if (random_int(0,1) === 0) {
 $test('query');
 } else {
 $test('prepare');
 }
 $i++;
}

foreach ($logs as $type =&gt; $log) {
 $total = 0;
 foreach ($log as $record) {
 $total += $record;
 }
 $count = count($log);
 echo "($count) $type Average: ".$total/$count.PHP_EOL;
}
</code></pre>

I've played with multiple different trial and counts in my specific environment, and consistently get between 20-30% faster results with <code>query</code> than <code>prepare</code>/<code>execute</code>

<blockquote>
 5.8128969669342 prepare 
 5.8688418865204 prepare 
 4.2948560714722 query 
 4.9533629417419 query 
 5.9051351547241 prepare 
 4.332102060318 query 
 5.9672858715057 prepare 
 5.0667371749878 query 
 3.8260300159454 query 
 4.0791549682617 query 
 4.3775160312653 query 
 3.6910600662231 query 
 5.2708210945129 prepare 
 6.2671611309052 prepare 
 7.3791449069977 prepare 
 (7) prepare Average: 6.0673267160143 
 (8) query Average: 4.3276024162769 
</blockquote>

I'm curious to see how this test compares in other environments, like MySQL.

Are they both do the same thing, only differently?

Is there any difference besides using <code>prepare</code> between

<pre><code>$sth = $db-&gt;query("SELECT * FROM table");
$result = $sth-&gt;fetchAll();
</code></pre>

and 

<pre><code>$sth = $db-&gt;prepare("SELECT * FROM table");
$sth-&gt;execute();
$result = $sth-&gt;fetchAll();
</code></pre>

?

PDO's query vs execute

SQL Server

服务器

Ubuntu

MySQL

Linux

他们是在做同样的事情，只是不同而已？除了使用prepare之外，还有什么区别吗？$sth = $db->query("SELECT * FROM table");$result = $sth->fetchAll();和$sth = $db->prepare("SELECT * FROM table");$sth->ex...

问PDO的查询与执行
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问PDO的查询与执行EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问PDO的查询与执行
EN