Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

DBI do() SQL injection

by Anonymous Monk
on Oct 19, 2023 at 11:53 UTC ( [id://11155043]=perlquestion: print w/replies, xml ) Need Help??

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

Is DBI do() method susceptible to sql injection,since you can't use the '?' placeholders unlike prepare()?

Replies are listed 'Best First'.
Re: DBI do() SQL injection
by hippo (Bishop) on Oct 19, 2023 at 12:12 UTC

    Of course you can use placeholders with do(). What makes you think otherwise?


    🦛

      what?? can you give an example of that?

        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;

        It is always beneficial to read the documentation.


        🦛

Re: DBI do() SQL injection
by ikegami (Patriarch) on Oct 20, 2023 at 16:12 UTC

    Everything that expects a string to be in a certain format is "susceptible to injection", since an injection bug is simply another term for incorrectly constructing a string.

    Since do expects SQL, it is susceptible to being provided incorrectly-built SQL, or susceptible to injection bugs as you put it.

    (Same goes for eval EXPR, qr//, decode_json, printf, etc)

    Example of a bug:

    my $sql = qq{ insert into customers ( id, name ) values ( '$id', '$name' ) -- XXX BUG }; $dbh->do( $sql );

    We can fix the bug by properly building the SQL statement.

    my $id_sql = $dbh->quote( $id ); my $name_sql = $dbh->quote( $name ); my $sql = qq{ insert into customers ( id, name ) values ( $id_sql, $name_sql ) }; $dbh->do( $sql );

    It's simpler using placeholders.

    my $sql = qq{ insert into customers ( id, name ) values ( ?, ? ) }; $dbh->do( $sql, undef, $id, $name );
Re: DBI do() SQL injection
by einhverfr (Friar) on Oct 29, 2023 at 05:25 UTC

    I think there is something of a misunderstanding here. Although do() can use placeholders, many statements cannot use placeholders.

    This means that there is a possible SQL injection vector where placeholders cannot be used. For example, in PostgreSQL, utility statements such as CREATE USER, CREATE TABLE, DROP TABLE, etc. have no query plan associated with them and therefore cannot be parameterized. In these cases, you have to properly use quote_identifier and quote methods to properly escape your variables.

    There are some things you can do to allow these to be parameterized from the Perl side such as wrapping in user-defined functions and then calling with SELECT queries, but this pushes into the function definition level on the SQL side.

    It's important to understand and think through how SQL Injection happens to assess it. In Postgres, for example, parameterized queries come in with the placeholder-including text and the parameters supplied separately. This means that the parsing of the query happens the parameters are not considered and the parameters are fed into the parse tree after the query is parsed but before it is planned. In this regard, there is no room for sql injection at all on this level. But anywhere you have to have string interpolation, you get the parsing happening once the variables are interpolated in, so this can affect the output parse tree. Once that happens, you have to use other measures to prevent that. In queries that are not planned, in Postgres this means that all utility statements are problems.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11155043]
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others sharing their wisdom with the Monastery: (8)
As of 2024-04-14 13:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found