php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Doc Bug #65370 Incorrect description for native prepared query
Submitted: 2013-08-01 11:38 UTC Modified: 2017-01-28 12:25 UTC
From: col dot shrapnel at gmail dot com Assigned:
Status: Verified Package: MySQLi related
PHP Version: Irrelevant OS:
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: col dot shrapnel at gmail dot com
New email:
PHP Version: OS:

 

 [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

Add a Patch

Pull Requests

Add a Pull Request

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
 
PHP Copyright © 2001-2018 The PHP Group
All rights reserved.
Last updated: Tue Nov 13 04:01:25 2018 UTC