Hi Matt,
On Thu, Aug 6, 2015 at 12:46 PM, Matt Tait <matt.t...@gmail.com> wrote:
> I'll take a few of your points in turn.
>
> With regards to the fact that not all SQL queries are directly
> parameterizable, this is true. Structural parts of a query, such as table
> names, column names and complex conditions are hard to parameterize with
> "vanilla" prepared statements, and many developers like to abstract some of
> these structural parts of a SQL query into config files, and append
> additional conditional constraints into the query at runtime based on user
> input.
>
> This feature addresses this head on. So long as the structural parts of the
> prepared statement -- that is, table names, database names and column names
> -- are not themselves attacker-controlled (I can't think of a valid reason
> whey they would be), this feature is happy for developers to concatenate
> them into a query string. For example, the following is not detected by the
> feature as dangerous, because the query (whilst dynamically constructed) is
> the linear concatenation of string literals, and so is a safeconst.
>
> $query = "SELECT * from {$config['tablename']} WHERE id=?";
> if(isset($_GET["filterbycolor"]))
> $query .= " AND color=?";
> do_prepared_statement($query, array("id" => $_GET["id"] "color" =>
> $_GET["color"]));
>
If you would like to prevent SQL injection via "Identifiers", how about
extend prepared query like
$query = "SELECT * from @ WHERE id=?";
if(isset($_GET["filterbycolor"]))
$query .= " AND color=?";
do_prepared_statement($query, array("id" => $_GET["id"] "color" =>
$_GET["color"]), array($config['tablename']));
where @ indicates identifier placeholder. The char could be anything that
will not violate SQL syntax. This could be implemented in user land as
there is no standard/native API for identifier placeholder.
Even if there is identifier placeholder, SQL keyword remains.
So to be perfect, you'll need another place holder for SQL keywords.
There is no escaping for SQL keywords and it has to be validation.
e.g. ORDER BY {$_GET['order']}
Regards,
--
Yasuo Ohgaki
yohg...@ohgaki.net
