mephit has asked for the wisdom of the Perl Monks concerning the following question:

Greetings, Monks.

OK, I've been reading here on the Monestary and elsewhere that it's a Good Idea to use placeholders in an SQL query where information for the query is derived from CGI form input, in case someone were to enter "; drop table foo;" or some such. So I'll go rewrite bits of my code using placeholders. No problems there.

What I want to do is write "bad" code and submit "bad" data so that I can see for myself the effects of using bad code, and its fix. But, in this instance, I can't do it.

From what I've heard, the DBI->prepare() method will only take a single statement, as will the DBI->do() method. So, how can these be exploited? I keep getting SQL syntax errors, even though the resulting multiple-statement string works just fine when entered directly into MySQL. (For instance, if I end up passing "select object_id, object_title from testac where object_title = 'test'; insert into testad values (49, 'blarg');" to do() or prepare(), it complains.) I just can't quite figure out what exactly *not* to do, and how such a badly-written script can be exploited.

So, can anybody write some "bad" code for me, and explain just how it can be taken advantage of? I have the following test code, assuming the form has already been submitted, and left untainted: 

my $data = $cgi->param('userdata');
my $sql = 'select col1, col2 from tablename';

[download]
What comes next? Thanks for any replies, folks.

Replies are listed 'Best First'.
Re: CGI/DBI and placeholders, revisited.
by mpeppler (Vicar) on May 04, 2002 at 20:58 UTC
    I don't know if this can be exploited directly with MySQL - I don't know that system well enough (actually I only know very little about it :-).

    However - if the back end is Sybase or MS-SQL then you can execute multiple statements in a single batch. The SQL statements need only be separated by white space in this case.

    Michael

[reply]
Re: CGI/DBI and placeholders, revisited.
by runrig (Abbot) on May 06, 2002 at 03:49 UTC
    Consider:
    my $sql = "select blargh from foo where bar = '".$bar_value."'";
my $bar_value = param('bar');
my $sth = $dbh->prepare($sql);

    [download]
    Then someone enters in the form for 'bar':
    "somevalue'
union
select blech from some_really_big_table
where some_unindexed_column = 'blah"
#or
"somevalue'
and some_field in
(select field
from some_other_really big table)
and bar = 'somevalue"

    [download]
    This won't corrupt your database (unless maybe you run out of temp space and something horrible happens), but you'll at least be spending more CPU cycles than you expected... 
    ------------
ooo  O\O  ooo tilly was here :,(
[reply]
[d/l]
[select]
Re: CGI/DBI and placeholders, revisited.
by BUU (Prior) on May 04, 2002 at 20:29 UTC
    Well, ive noticed on win32 perl/apache/mysql, that mysql or dbi or somesuch dies when you try to call a method on sql containing a semicolon. I.e. prepare("select * from my_table"); works but prepare("select * from my_table;"); tends to die on me.
[reply]
      You've got to remember that the ';' is not part of the SQL syntax. It's interpreted by the command line tool that you use to access MySQL (or Oracle, or Sybase...) as a statement separator.

      Michael

[reply]
        OK, well, if this is the case, and neither do() nor prepare() will work if given a string with a semicolon (as if someone had tried to insert a second statement in the form), then why all the hubbub about using placeholders and worrying about someone trying to execute extra commands? Or am I missing something? (I probably am.) Can somebody point me to an explanation somewhere? Thanks.
[reply]
      A reply falls below the community's threshold of quality. You may see it by logging in.

Back to Seekers of Perl Wisdom