StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The short answer is <strong>NO</strong>, PDO prepares will not defend you from all possible SQL-Injection attacks. For certain obscure edge-cases.</p> <p>I'm adapting <a href="https://stackoverflow.com/a/12118602/338665">this answer</a> to talk about PDO...</p> <p>The long answer isn't so easy. It's based off an attack <a href="http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string" rel="noreferrer">demonstrated here</a>.</p> <h1>The Attack</h1> <p>So, let's start off by showing the attack...</p> <pre><code>$pdo->query('SET NAMES gbk'); $var = "\xbf\x27 OR 1=1 /*"; $query = 'SELECT * FROM test WHERE name = ? LIMIT 1'; $stmt = $pdo->prepare($query); $stmt->execute(array($var)); </code></pre> <p>In certain circumstances, that will return more than 1 row. Let's dissect what's going on here:</p> <ol> <li><p><strong>Selecting a Character Set</strong></p> <pre><code>$pdo->query('SET NAMES gbk'); </code></pre> <p>For this attack to work, we need the encoding that the server's expecting on the connection both to encode <code>'</code> as in ASCII i.e. <code>0x27</code> <em>and</em> to have some character whose final byte is an ASCII <code>\</code> i.e. <code>0x5c</code>. As it turns out, there are 5 such encodings supported in MySQL 5.6 by default: <code>big5</code>, <code>cp932</code>, <code>gb2312</code>, <code>gbk</code> and <code>sjis</code>. We'll select <code>gbk</code> here.</p> <p>Now, it's very important to note the use of <code>SET NAMES</code> here. This sets the character set <strong>ON THE SERVER</strong>. There is another way of doing it, but we'll get there soon enough.</p></li> <li><p><strong>The Payload</strong></p> <p>The payload we're going to use for this injection starts with the byte sequence <code>0xbf27</code>. In <code>gbk</code>, that's an invalid multibyte character; in <code>latin1</code>, it's the string <code>¿'</code>. Note that in <code>latin1</code> <strong>and</strong> <code>gbk</code>, <code>0x27</code> on its own is a literal <code>'</code> character. </p> <p>We have chosen this payload because, if we called <code>addslashes()</code> on it, we'd insert an ASCII <code>\</code> i.e. <code>0x5c</code>, before the <code>'</code> character. So we'd wind up with <code>0xbf5c27</code>, which in <code>gbk</code> is a two character sequence: <code>0xbf5c</code> followed by <code>0x27</code>. Or in other words, a <em>valid</em> character followed by an unescaped <code>'</code>. But we're not using <code>addslashes()</code>. So on to the next step...</p></li> <li><p><strong>$stmt->execute()</strong></p> <p>The important thing to realize here is that PDO by default does <strong>NOT</strong> do true prepared statements. It emulates them (for MySQL). Therefore, PDO internally builds the query string, calling <code>mysql_real_escape_string()</code> (the MySQL C API function) on each bound string value.</p> <p>The C API call to <code>mysql_real_escape_string()</code> differs from <code>addslashes()</code> in that it knows the connection character set. So it can perform the escaping properly for the character set that the server is expecting. However, up to this point, the client thinks that we're still using <code>latin1</code> for the connection, because we never told it otherwise. We did tell the <em>server</em> we're using <code>gbk</code>, but the <em>client</em> still thinks it's <code>latin1</code>.</p> <p>Therefore the call to <code>mysql_real_escape_string()</code> inserts the backslash, and we have a free hanging <code>'</code> character in our "escaped" content! In fact, if we were to look at <code>$var</code> in the <code>gbk</code> character set, we'd see:</p> <pre>縗' OR 1=1 /*</pre> <p>Which is exactly what the attack requires.</p></li> <li><p><strong>The Query</strong></p> <p>This part is just a formality, but here's the rendered query:</p> <pre class="lang-sql prettyprint-override"><code>SELECT * FROM test WHERE name = '縗' OR 1=1 /*' LIMIT 1 </code></pre></li> </ol> <p>Congratulations, you just successfully attacked a program using PDO Prepared Statements...</p> <h1>The Simple Fix</h1> <p>Now, it's worth noting that you can prevent this by disabling emulated prepared statements:</p> <pre><code>$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); </code></pre> <p>This will <em>usually</em> result in a true prepared statement (i.e. the data being sent over in a separate packet from the query). However, be aware that PDO will silently <a href="https://github.com/php/php-src/blob/master/ext/pdo_mysql/mysql_driver.c#L210" rel="noreferrer">fallback</a> to emulating statements that MySQL can't prepare natively: those that it can are <a href="http://dev.mysql.com/doc/en/sql-syntax-prepared-statements.html" rel="noreferrer">listed</a> in the manual, but beware to select the appropriate server version).</p> <h1>The Correct Fix</h1> <p>The problem here is that we didn't call the C API's <code>mysql_set_charset()</code> instead of <code>SET NAMES</code>. If we did, we'd be fine provided we are using a MySQL release since 2006.</p> <p>If you're using an earlier MySQL release, then a <a href="http://bugs.mysql.com/bug.php?id=8378" rel="noreferrer">bug</a> in <code>mysql_real_escape_string()</code> meant that invalid multibyte characters such as those in our payload were treated as single bytes for escaping purposes <em>even if the client had been correctly informed of the connection encoding</em> and so this attack would still succeed. The bug was fixed in MySQL <a href="http://dev.mysql.com/doc/refman/4.1/en/news-4-1-20.html" rel="noreferrer">4.1.20</a>, <a href="http://dev.mysql.com/doc/relnotes/mysql/5.0/en/news-5-0-22.html" rel="noreferrer">5.0.22</a> and <a href="http://dev.mysql.com/doc/relnotes/mysql/5.1/en/news-5-1-11.html" rel="noreferrer">5.1.11</a>.</p> <p>But the worst part is that <code>PDO</code> didn't expose the C API for <code>mysql_set_charset()</code> until 5.3.6, so in prior versions it <strong>cannot</strong> prevent this attack for every possible command! It's now exposed as a <a href="http://www.php.net/manual/en/ref.pdo-mysql.connection.php" rel="noreferrer">DSN parameter</a>, which should be used <strong>instead of</strong> <code>SET NAMES</code>...</p> <h1>The Saving Grace</h1> <p>As we said at the outset, for this attack to work the database connection must be encoded using a vulnerable character set. <a href="http://dev.mysql.com/doc/en/charset-unicode-utf8mb4.html" rel="noreferrer"><code>utf8mb4</code></a> is <em>not vulnerable</em> and yet can support <em>every</em> Unicode character: so you could elect to use that instead—but it has only been available since MySQL 5.5.3. An alternative is <a href="http://dev.mysql.com/doc/en/charset-unicode-utf8.html" rel="noreferrer"><code>utf8</code></a>, which is also <em>not vulnerable</em> and can support the whole of the Unicode <a href="http://en.wikipedia.org/wiki/Plane_(Unicode)#Basic_Multilingual_Plane" rel="noreferrer">Basic Multilingual Plane</a>.</p> <p>Alternatively, you can enable the <a href="http://dev.mysql.com/doc/en/sql-mode.html#sqlmode_no_backslash_escapes" rel="noreferrer"><code>NO_BACKSLASH_ESCAPES</code></a> SQL mode, which (amongst other things) alters the operation of <code>mysql_real_escape_string()</code>. With this mode enabled, <code>0x27</code> will be replaced with <code>0x2727</code> rather than <code>0x5c27</code> and thus the escaping process <em>cannot</em> create valid characters in any of the vulnerable encodings where they did not exist previously (i.e. <code>0xbf27</code> is still <code>0xbf27</code> etc.)—so the server will still reject the string as invalid. However, see <a href="https://stackoverflow.com/a/23277864/623041">@eggyal's answer</a> for a different vulnerability that can arise from using this SQL mode (albeit not with PDO).</p> <h1>Safe Examples</h1> <p>The following examples are safe:</p> <pre><code>mysql_query('SET NAMES utf8'); $var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*"); mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1"); </code></pre> <p>Because the server's expecting <code>utf8</code>...</p> <pre><code>mysql_set_charset('gbk'); $var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*"); mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1"); </code></pre> <p>Because we've properly set the character set so the client and the server match.</p> <pre><code>$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $pdo->query('SET NAMES gbk'); $stmt = $pdo->prepare('SELECT * FROM test WHERE name = ? LIMIT 1'); $stmt->execute(array("\xbf\x27 OR 1=1 /*")); </code></pre> <p>Because we've turned off emulated prepared statements.</p> <pre><code>$pdo = new PDO('mysql:host=localhost;dbname=testdb;charset=gbk', $user, $password); $stmt = $pdo->prepare('SELECT * FROM test WHERE name = ? LIMIT 1'); $stmt->execute(array("\xbf\x27 OR 1=1 /*")); </code></pre> <p>Because we've set the character set properly.</p> <pre><code>$mysqli->query('SET NAMES gbk'); $stmt = $mysqli->prepare('SELECT * FROM test WHERE name = ? LIMIT 1'); $param = "\xbf\x27 OR 1=1 /*"; $stmt->bind_param('s', $param); $stmt->execute(); </code></pre> <p>Because MySQLi does true prepared statements all the time.</p> <h1>Wrapping Up</h1> <p>If you:</p> <ul> <li>Use Modern Versions of MySQL (late 5.1, all 5.5, 5.6, etc) <strong>AND</strong> PDO's DSN charset parameter (in PHP ≥ 5.3.6)</li> </ul> <p><strong>OR</strong></p> <ul> <li>Don't use a vulnerable character set for connection encoding (you only use <code>utf8</code> / <code>latin1</code> / <code>ascii</code> / etc)</li> </ul> <p><strong>OR</strong></p> <ul> <li>Enable <code>NO_BACKSLASH_ESCAPES</code> SQL mode</li> </ul> <p>You're 100% safe.</p> <p>Otherwise, you're vulnerable <strong>even though you're using PDO Prepared Statements...</strong></p> <h1>Addendum</h1> <p>I've been slowly working on a patch to change the default to not emulate prepares for a future version of PHP. The problem that I'm running into is that a LOT of tests break when I do that. One problem is that emulated prepares will only throw syntax errors on execute, but true prepares will throw errors on prepare. So that can cause issues (and is part of the reason tests are borking).</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload