This RFC proposes to add a new method mysqli::quote_string() to PHP, which should fix the last remaining SQL injection loophole and ease the development for users who require a manual escaping mechanism.
<?php "'Don\\'t!'" === $link->quote_string("Don't!");
<?php public function quote_string(string $string): string {} // AND function mysqli_quote_string(mysqli $mysql, string $string): string {}
While we certainly hope that most users choose prepared statements to avoid SQL injection completely, there are projects which choose manual escaping either because of some design choice or because of the nature of the project, e.g. phpMyAdmin. Manual escaping should remain part of the mysqli extension, and so this proposal suggests improvements that will make it easier, less error-prone, and more secure against SQL injection.
This function exists in PDO and is called PDO::quote(). This RFC proposes adding the same function to mysqli. The function will escape the input just like mysqli::real_escape_string and wrap the output in single quotation marks. This will help to avoid accidentally forgetting the quotation marks in users' code. The only valid use of mysqli::real_escape_string() is when wrapped in single quotation marks (or double quotation marks; more on this later). Currently, it requires that users use functions such as sprintf and put the quotation marks manually.
<?php $sql = sprintf("SELECT '%s'", $mysqli->real_escape_string($value)); $sql = sprintf('SELECT "%s"', $mysqli->real_escape_string($value));
This represents 100% of valid use cases. Due to this and its lengthy and convoluted name, it's very easy to inadvertently introduce a critical security vulnerability into your code. It's an unnecessary burden on PHP developers.
The MySQL team got the escaping mechanism wrong on multiple occasions, which can still be seen in the names of its functions: mysql_real_escape_string_quote and mysql_real_escape_string. The mysql_real_escape_string_quote was added as the latest attempt to fix the SQL injection vulnerabilities. PHP has never fully adopted this new function.
PHP's mysqli::real_escape_string has a well-known SQL injection vulnerability when using double quotation marks. The only way to fix it is to introduce a new function or a new parameter. Instead of following MySQL's path, this RFC takes from PDO's example instead. MySQL's new function adds a new parameter which requires users to specify the quoting character, which it then doubles in the string. PDO, on the other hand, is safe from this vulnerability because it doesn't offer the possibility of choosing the quotation character. It is always the single quote. This is also much easier for users, as shown earlier. Double quotation marks are non-standard SQL, which is why the new function will always wrap the text in single quotation marks.
Simple example which avoids SQL injection present in current PHP:
<?php $mysqli->query('SET @@sql_mode="NO_BACKSLASH_ESCAPES"'); $value = '" OR 1=1 -- foo'; $sql = sprintf('SELECT * FROM foo WHERE name=%s', $mysqli->quote_string($value)); $mysqli->query($sql);
The name was chosen carefully to clearly represent what the function is for. It resembles its namesake in PDO PDO::quote(), but adds the “string” part to clearly indicate to the users that this function is only for strings. The parameter type allows only for strings to be passed to this function, but in non-strict mode, it is possible to pass other types, which is a programming error. This wasn't much of a problem in PDO, but in mysqli this newbie mistake was repeated many times and exacerbated by online tutorials of questionable quality. For this reason, the parameter type is clearly stated in the name of the function. It also aligns with the original MySQL's names.
This RFC does not introduce any backwards-incompatible breaks.
PHP 8.6
Ideally, the mysqli::real_escape_string function should be deprecated in the next minor version after this RFC gets implemented. However, this is not part of this RFC and remains up for discussion as to when or if it will be deprecated. The hopes are that the new function will replace it completely.
I know it's going to be probably impossible to convince the PHP community to deprecate mysqli::real_escape_string in the same release that mysqli::quote_string will be added in, but without doing so, a potential issue arises where the developers will be asked to remove the manually placed quotation marks when migrating to the new function. This means that they could accidentally remove the quotation marks and forget to replace the function, resulting in an SQL injection.
PHP's ecosystem will need to replace the old function with the new one and remove the manually placed quotation marks. It's simple for a self-contained project, but will create some effort for library maintainers. There are some frameworks which exposed the mysqli::real_escape_string functionality to their users without automatically adding the quotation marks. This will make it significantly more difficult for them to adopt the new function, but it also opens up a chance to review the usage and address any invalid examples.
Only mysqli extension is affected.
Pick a title that reflects the concrete choice people will vote on.
Please consult the php/policies repository for the current voting guidelines.
Primary Vote requiring a 2/3 majority to accept the RFC:
After the RFC is implemented, this section should contain:
Keep this updated with features that were discussed on the mail lists.
If there are major changes to the initial proposal, please include a short summary with a date or a link to the mailing list announcement here, as not everyone has access to the wikis' version history.