in reply to Re^2: DBI do() SQL injection
in thread DBI do() SQL injection

Of course I can - here's the one from the documentation:

my $rows_deleted = $dbh->do(q{
    DELETE FROM table
    WHERE status = ?
}, undef, 'DONE') or die $dbh->errstr;

[download]

It is always beneficial to read the documentation.

🦛

Replies are listed 'Best First'.
Re^4: DBI do() SQL injection
by Anonymous Monk on Oct 19, 2023 at 12:47 UTC
    doh...thanks! In all the other occasions like 
    $dbh->do(qq{ 
insert into customers
(id,name)
values('$id','$name')}
)

    [download]
    can you get injected?
[reply]
[d/l]
      If you have no control over the contents of $id, you shouldn't use it directly. What if $id was set to 42', 'Batman'); drop table customers; --?

      In fact, I tried it with two different database drivers. Interestingly, in DBD::SQLite, the table wasn't removed, as it doesn't support multiple statements at a time. On the other hand, DBD::Pg happily removed the table and crashed when I tried using it later.

      Read more... (2 kB)

      You can always fix that by wrapping the value into a quote: 

          $dbh->do(join "",
             'INSERT INTO customers(id,name) VALUES(',
             $dbh->quote($id), ', ',
             $dbh->quote($name),
             ')');

      [download]

      map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]
[reply]
[d/l]
[select]
        If you have no control over the contents of $id, you shouldn't use it directly.

        Even if you have control, you should use placeholders. Keeping the query string constant and only varying the parameters allows everything below DBI (i.e. the DBD and especially the database engine) to cache the already parsed query string, including all optimizations. Changing the query string for every new value of $id prevents that.

        Alexander

        --
        Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
[reply]
[d/l]
        You can always fix that by wrapping the value into a quote
        And is there any advantage to using quote instead of placeholders? 
        sub run_do_with_placeholders($dbh, $id, $name) {
    $dbh->do(qq{
        INSERT INTO customers
        (id,name)
        VALUES(?, ?)},
        undef, $id, $name);
}

        [download]
        There are a few places (table and column names, mostly) where you're required to use quote instead of placeholders, but, IMO, you should always use placeholders for data values where possible and never rely on quoting unless you absolutely have to.
[reply]
[d/l]
[select]

        Even if the table isn't dropped, it's still an injection bug since the code did not behave as desired (i.e. did not use  42', 'Batman'); drop table customers; -- as the id).

[reply]
[d/l]
        How does dbh quote save the day? What is different from simple quotinq with '' or q or qq ?
[reply]

      Yes, so don't do that! :-)

      🦛

[reply]

In Section Seekers of Perl Wisdom