Doc #65370 [Ver]: Incorrect description for native prepared query
Edit report at https://bugs.php.net/bug.php?id=65370&edit=1
ID: 65370
User updated by: col dot shrapnel at gmail dot com
Reported by: col dot shrapnel at gmail dot com
Summary: Incorrect description for native prepared query
Status: Verified
Type: Documentation Problem
Package: Documentation problem
PHP Version: Irrelevant
Block user comment: N
Private report: N
New Comment:
> 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*.
Previous Comments:
------------------------------------------------------------------------
[2013-08-02 01:44:49] 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-01 11:38:16] 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.
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=65370&edit=1
Thread (4 messages)