php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Doc Bug #65370 Incorrect description for native prepared query
Submitted: 2013-08-01 11:38 UTC Modified: 2021-04-08 14:37 UTC
From: col dot shrapnel at gmail dot com Assigned: dharman (profile)
Status: Closed Package: MySQLi related
PHP Version: Irrelevant OS:
Private report: No CVE-ID: None
View Developer Edit
 [2013-08-01 11:38 UTC] col dot shrapnel at gmail dot com 
Description:
------------
---
From manual page: http://www.php.net/mysqli.quickstart.prepared-statements
---

Some bullshit is written on this documentation page and it have to be changed.


Test script:
---------------
The page at the moment says:

Bound variables will be escaped automatically by the server. The server inserts their escaped values at the appropriate places into the statement template before execution. A hint must be provided to the server for the type of bound variable, to create an appropriate conversion. See the mysqli_stmt_bind_param() function for more information.

The automatic escaping of values within the server is sometimes considered a security feature to prevent SQL injection. The same degree of security can be achieved with non-prepared statements, if input values are escaped correctly. 

Expected result:
----------------
While it have to say something like this

"Bound variables are sent to the server completely separated from the query and thus cannot interfere with it. The server uses these values directly at the stage of execution, after having statement template parsed. No escaping ever required for the prepared values as they never being part of the query. A hint must be provided to the server for the type of bound variable, to create an appropriate conversion. See the mysqli_stmt_bind_param() function for more information.

Such a separation sometimes considered as the only security feature to prevent SQL injection. But the same degree of security can be achieved with non-prepared statements, if all the SQL literals are formatted correctly. It have to be noted that correct formatting is not the same as escaping and involves much more measures than simple escaping. Thus, it is more convenient and error-proof to use prepared statements". 

Actual result:
--------------
The actual result of the current statement is 1000s of poor PHP monkeys who were confused about escaping for ages, thanks for bullshit from mysql_real_escape_string manual page ( https://bugs.php.net/bug.php?id=60398 ) and still under the same delusion thanks to this one.

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2013-08-02 01:44 UTC] yohgaki@php.net
-Summary: "Escaping" bullshit again. +Summary: Incorrect description for native prepared query -Status: Open +Status: Verified
 [2013-08-02 01:44 UTC] yohgaki@php.net 
Native prepared query send parameters only to query db. 

With native prepared query, parameters will not be escaped, but simply passed to 
db as parameters. There is no need to be escaped since it is not a SQL string.

BTW, some users misunderstand that prepared query solves all of SQL injection 
problems. If identifiers(e.g. table/field names) are parameters, they must be 
escaped. If SQL keywords (e.g. ORDER BY DESC, etc) are passed as parameter, they 
should be validated.

It would be better to write out all possible SQL injection countermeasures, 
including escaping parameters. Many users failed to escape parameters IN query, 
etc.
 [2013-08-02 08:33 UTC] col dot shrapnel at gmail dot com 
> Many users failed to escape parameters IN query

Escaping is WRONG word as it's strongly connected to notorious 
mysql(i)_real_escape_string. And thus should never be used in the context of
injection prevention. A word "formatting" have to be used instead, along with 
special note saying "escaping is good for strings only, while all other SQL 
literals require completely different formatting". With a table like this 
provided:

1. Strings
 * have to be added via native prepared statement     
or
 * have to be enclosed in quotes
 * special characters (frankly - the very delimiting quotes) have to be escaped
 * proper client encoding have to be set   
2. Numbers
 * have to be added via native prepared statement     
or
 * should be formatted to contain only numbers, a decimal delimiter and a sign
3. Identifiers
 * have to be enclosed in backticks
 * special characters (frankly - the very delimiting backticks) have to be 
escaped
4. Operators and keywords. 
 * there are no special formatting rules for the keywords and operators beside 
the fact that they have to be legitimate SQL operators and keywords, but not 
anything else. So, they have to be *whitelisted*.
 [2017-01-28 12:25 UTC] cmb@php.net
-Package: Documentation problem +Package: MySQLi related
 [2021-04-08 14:37 UTC] dharman@php.net
-Status: Verified +Status: Closed -Assigned To: +Assigned To: dharman
 [2021-04-08 14:37 UTC] dharman@php.net 
I am closing this incident as the description has been changed as proposed back in 2013 [1]. I don't think there is anything else to be done here. If you think that the description can still be improved upon then please submit a PR on GitHub with suggested changes and we will review it. 

[1]: https://github.com/php/doc-en/commit/ee0091371387622a6b8a6c75cfaf6bebe2f7c4e7
 
PHP Copyright © 2001-2025 The PHP Group
All rights reserved. 		Last updated: Thu May 08 13:01:27 2025 UTC