[Gllug] Introductions to Databases

[James Courtier-Dutton]

2009/7/19 James Laver <gllug at jameslaver.com>:
>
> mysql_query("SELECT * FROM table WHERE col = '" . $_GET['val'] . "'",
> $connection);
>
> Doesn't take a genius to work out that one's wrong.
>
> mysql_query("SELECT * FROM table WHERE col = '" .
> mysql_real_escape_string($_GET['val']) . "'",$connection);
>
> That one will at least escape the argument.
>
> Then they came up with the mysqli library which supports bound
> parameters. It also supports the older style.
>
>
> On the other hand, it has PDO which is a lot more like Perl DBI. But
> then Perl DBI can be abused too:
>
> $dbh->do("SELECT * FROM table WHERE col= '" . $my_unescaped_val . "'");
>
> So levelling criticisms at a language for providing the means to use a
> database is hardly correct. While it may be true that Perl users
> generally avoid the crap way of doing things, it's not like the
> language has a part to play in that, in the same way that PHP is just
> providing the tools.
>

This highlights the point I was trying to make and my dislike of SQL.
Essentially, one wishes to do:
SELECT * FROM table WHERE col= USER_ENTERED_PARAM

Now, unless the language you are using to make the SQL query takes the
user_entered_param, escapes it etc, and only then passes it to the SQL
query will one not get an exploit injection.
My argument is that the database query language should not need all
this escaping of the user params. The database query language should
specifically identify "user_entered_param" locations in the query and
process the query appropriately.

Now, how to do this is a problem in itself, but probably solvable.
The complexity lies in the fact that SQL is designed to have the
results of one query be possible input params to another query. I.e.
Nested SQL queries.

James
-- 
Gllug mailing list  -  Gllug at gllug.org.uk
http://lists.gllug.org.uk/mailman/listinfo/gllug