Is prepare statement enough for SQL injection?

Discussion in 'Perl Misc' started by xhoster@gmail.com, Jun 5, 2006.

  1. Guest

    "howa" <> wrote:
    > If I use prepare statement in every dbh call (mysql), will all chances
    > of SQL injection can be prevented?


    No, prepared statements are not magical (in fact they don't even really
    exist for mysql, they are emulated by the DBI/DBD modules). They do make
    it easier to write safer code, because they facilitate use of bind
    variables. But you can write unsafe code with prepare just as easily as
    with selectall_arrayref or whatever.

    Xho

    --
    -------------------- http://NewsReader.Com/ --------------------
    Usenet Newsgroup Service $9.95/Month 30GB
     
    , Jun 5, 2006
    #1
    1. Advertising

  2. howa Guest

    If I use prepare statement in every dbh call (mysql), will all chances
    of SQL injection can be prevented?

    thanks...

    howa
     
    howa, Jun 5, 2006
    #2
    1. Advertising

  3. howa Guest

    any examples of SQL injection even if I use the prepare statement?

    thanks....

    寫é“:

    > "howa" <> wrote:
    > > If I use prepare statement in every dbh call (mysql), will all chances
    > > of SQL injection can be prevented?

    >
    > No, prepared statements are not magical (in fact they don't even really
    > exist for mysql, they are emulated by the DBI/DBD modules). They do make
    > it easier to write safer code, because they facilitate use of bind
    > variables. But you can write unsafe code with prepare just as easily as
    > with selectall_arrayref or whatever.
    >
    > Xho
    >
    > --
    > -------------------- http://NewsReader.Com/ --------------------
    > Usenet Newsgroup Service $9.95/Month 30GB
     
    howa, Jun 5, 2006
    #3
  4. Guest

    "howa" <> wrote:
    > any examples of SQL injection even if I use the prepare statement?


    Sure. Please give me an example of SQL injection without using prepare.
    I'll translate it to prepare in a way that doesn't solve the injection
    problem.

    Xho

    --
    -------------------- http://NewsReader.Com/ --------------------
    Usenet Newsgroup Service $9.95/Month 30GB
     
    , Jun 5, 2006
    #4
  5. okay, such as :

    my $username = "tom' ; DELETE FROM users";

    my $sqlstr = "SELECT * FROM users WHERE name = '$username'";

    How to re-produce this hack using prepare statement?

    thanks...


    wrote:
    > "howa" <> wrote:
    > > any examples of SQL injection even if I use the prepare statement?

    >
    > Sure. Please give me an example of SQL injection without using prepare.
    > I'll translate it to prepare in a way that doesn't solve the injection
    > problem.
    >
    > Xho
    >
    > --
    > -------------------- http://NewsReader.Com/ --------------------
    > Usenet Newsgroup Service $9.95/Month 30GB
     
    Tsz Ming Wong, Jun 5, 2006
    #5
  6. Guest

    Couldn't you just use "quote" to backslash all illegal characters in a
    variable for mySQL to prevent such a thing..

    my $safe_var = $dbh->quote($my_var);
    my $sqlstr = qq{SELECT * FROM users WHERE name = $safe_var;};
    $sth = $dbh->prepare($sqlstr);

    Tsz Ming Wong wrote:
    > okay, such as :
    >
    > my $username = "tom' ; DELETE FROM users";
    >
    > my $sqlstr = "SELECT * FROM users WHERE name = '$username'";
    >
    > How to re-produce this hack using prepare statement?
    >
    > thanks...
    >
    >
    > wrote:
    > > "howa" <> wrote:
    > > > any examples of SQL injection even if I use the prepare statement?

    > >
    > > Sure. Please give me an example of SQL injection without using prepare.
    > > I'll translate it to prepare in a way that doesn't solve the injection
    > > problem.
    > >
    > > Xho
    > >
    > > --
    > > -------------------- http://NewsReader.Com/ --------------------
    > > Usenet Newsgroup Service $9.95/Month 30GB
     
    , Jun 5, 2006
    #6
  7. howa Guest

    there is no need to...

    my $name = "tom";
    my $sqlstr = "SELECT * FROM users WHERE name = ? ";
    my $sth = $dbh->prepare($sqlstr);
    my $rc = $sth->execute($name);

    i just wonder how to hack the above prepare codes using SQL injection?


    寫é“:

    > Couldn't you just use "quote" to backslash all illegal characters in a
    > variable for mySQL to prevent such a thing..
    >
    > my $safe_var = $dbh->quote($my_var);
    > my $sqlstr = qq{SELECT * FROM users WHERE name = $safe_var;};
    > $sth = $dbh->prepare($sqlstr);
    >
    > wrote:
    > > okay, such as :
    > >
    > > my $username = "tom' ; DELETE FROM users";
    > >
    > > my $sqlstr = "SELECT * FROM users WHERE name = '$username'";
    > >
    > > How to re-produce this hack using prepare statement?
    > >
    > > thanks...
    > >
    > >
    > > wrote:
    > > > "howa" <> wrote:
    > > > > any examples of SQL injection even if I use the prepare statement?
    > > >
    > > > Sure. Please give me an example of SQL injection without using prepare.
    > > > I'll translate it to prepare in a way that doesn't solve the injection
    > > > problem.
    > > >
    > > > Xho
    > > >
    > > > --
    > > > -------------------- http://NewsReader.Com/ --------------------
    > > > Usenet Newsgroup Service $9.95/Month 30GB
     
    howa, Jun 5, 2006
    #7
  8. Ben Morrow Guest

    [please learn to quote properly. Now.]

    Quoth "howa" <>:
    > :
    > > <> wrote:
    > > > wrote:
    > > > > "howa" <> wrote:
    > > > > >
    > > > > > any examples of SQL injection even if I use the prepare statement?
    > > > >
    > > > > Sure. Please give me an example of SQL injection without using
    > > > > prepare. I'll translate it to prepare in a way that doesn't
    > > > > solve the injection problem.
    > > >
    > > > okay, such as :
    > > >
    > > > my $username = "tom' ; DELETE FROM users";
    > > >
    > > > my $sqlstr = "SELECT * FROM users WHERE name = '$username'";
    > > >
    > > > How to re-produce this hack using prepare statement?

    > >
    > > Couldn't you just use "quote" to backslash all illegal characters in a
    > > variable for mySQL to prevent such a thing..

    >
    > there is no need to...
    >
    > my $name = "tom";
    > my $sqlstr = "SELECT * FROM users WHERE name = ? ";
    > my $sth = $dbh->prepare($sqlstr);
    > my $rc = $sth->execute($name);
    >
    > i just wonder how to hack the above prepare codes using SQL injection?


    Xho's point is that it is not the prepare that gives the safety here, it
    is the use of bind variables (the '?'). IIRC, you can use bind variables
    without prepare; certainly, you can create unsafe statements *with*
    prepare by interpolating variables straight into the prepared statement
    as your original example did.

    Ben

    --
    If you put all the prophets, | You'd have so much more reason
    Mystics and saints | Than ever was born
    In one room together, | Out of all of the conflicts of time.
    The Levellers, 'Believers'
     
    Ben Morrow, Jun 5, 2006
    #8
  9. Paul Lalli Guest

    Ben Morrow wrote:
    > [please learn to quote properly. Now.]
    >
    > Quoth "howa" <>:
    > > :
    > > > <> wrote:
    > > > > wrote:
    > > > > > "howa" <> wrote:
    > > > > > >
    > > > > > > any examples of SQL injection even if I use the prepare statement?
    > > > > >
    > > > > > Sure. Please give me an example of SQL injection without using
    > > > > > prepare. I'll translate it to prepare in a way that doesn't
    > > > > > solve the injection problem.
    > > > >
    > > > > okay, such as :
    > > > >
    > > > > my $username = "tom' ; DELETE FROM users";
    > > > >
    > > > > my $sqlstr = "SELECT * FROM users WHERE name = '$username'";
    > > > >
    > > > > How to re-produce this hack using prepare statement?
    > > >
    > > > Couldn't you just use "quote" to backslash all illegal characters in a
    > > > variable for mySQL to prevent such a thing..

    > >
    > > there is no need to...
    > >
    > > my $name = "tom";
    > > my $sqlstr = "SELECT * FROM users WHERE name = ? ";
    > > my $sth = $dbh->prepare($sqlstr);
    > > my $rc = $sth->execute($name);
    > >
    > > i just wonder how to hack the above prepare codes using SQL injection?

    >
    > Xho's point is that it is not the prepare that gives the safety here, it
    > is the use of bind variables (the '?'). IIRC, you can use bind variables
    > without prepare; certainly, you can create unsafe statements *with*
    > prepare by interpolating variables straight into the prepared statement
    > as your original example did.


    Er, you can? When I try to run such code, I get the error:
    DBD::Informix::db prepare failed: SQL: -555: Cannot use a select or any
    of the database statements in a multi-query prepare.

    Prepared statements must be *one* statement each.

    Of course, I get the same message if I try to use the same kind of
    query with a do() instead of a prepare()/execute(), so this is still
    not an example of prepare() saving you from anything.

    The kind of SQL injection attack that will not be prevented by
    prepare() (or do()) but will be prevented by bind variables is a
    scenario like this:
    my $user = "john' or '1' = '1";
    my $sql = "SELECT * from account_info WHERE username = '$user'";
    my $sth = $dbh->prepare($sql);
    $sth->execute();

    As opposed to:
    my $user = "john' or '1' = '1";
    my $sql = "SELECT * from account_info WHERE username = ?";
    my $sth = $dbh->prepare($sql);
    $sth->execute($user);

    Paul Lalli
     
    Paul Lalli, Jun 5, 2006
    #9
  10. howa Guest

    how to use bind variables without prepare ? i would like to know...

    thanks...

    Ben Morrow 寫é“:

    > [please learn to quote properly. Now.]
    >
    > Quoth "howa" <>:
    > > :
    > > > <> wrote:
    > > > > wrote:
    > > > > > "howa" <> wrote:
    > > > > > >
    > > > > > > any examples of SQL injection even if I use the prepare statement?
    > > > > >
    > > > > > Sure. Please give me an example of SQL injection without using
    > > > > > prepare. I'll translate it to prepare in a way that doesn't
    > > > > > solve the injection problem.
    > > > >
    > > > > okay, such as :
    > > > >
    > > > > my $username = "tom' ; DELETE FROM users";
    > > > >
    > > > > my $sqlstr = "SELECT * FROM users WHERE name = '$username'";
    > > > >
    > > > > How to re-produce this hack using prepare statement?
    > > >
    > > > Couldn't you just use "quote" to backslash all illegal characters in a
    > > > variable for mySQL to prevent such a thing..

    > >
    > > there is no need to...
    > >
    > > my $name = "tom";
    > > my $sqlstr = "SELECT * FROM users WHERE name = ? ";
    > > my $sth = $dbh->prepare($sqlstr);
    > > my $rc = $sth->execute($name);
    > >
    > > i just wonder how to hack the above prepare codes using SQL injection?

    >
    > Xho's point is that it is not the prepare that gives the safety here, it
    > is the use of bind variables (the '?'). IIRC, you can use bind variables
    > without prepare; certainly, you can create unsafe statements *with*
    > prepare by interpolating variables straight into the prepared statement
    > as your original example did.
    >
    > Ben
    >
    > --
    > If you put all the prophets, | You'd have so much more reason
    > Mystics and saints | Than ever was born
    > In one room together, | Out of all of the conflicts of time.
    > The Levellers, 'Believers'
     
    howa, Jun 5, 2006
    #10
  11. Paul Lalli Guest

    howa wrote:
    > Ben Morrow 寫é“:
    > > Xho's point is that it is not the prepare that gives the safety here, it
    > > is the use of bind variables (the '?'). IIRC, you can use bind variables
    > > without prepare; certainly, you can create unsafe statements *with*
    > > prepare by interpolating variables straight into the prepared statement
    > > as your original example did.


    > how to use bind variables without prepare ? i would like to know...


    First, please start quoting properly. This is not the first time
    you've been asked. Post your reply *below* what you are replying to.

    Second, read the DBI module's documentation. Take a look at, for
    example, the do() method:

    my $sql = "DELETE FROM users WHERE user_name = ?";
    $dbh->do($sql, undef, 'Mr. Itty');

    Paul Lalli
     
    Paul Lalli, Jun 5, 2006
    #11
  12. howa Guest

    Paul Lalli 寫é“:

    > howa wrote:
    > > Ben Morrow 寫é“:
    > > > Xho's point is that it is not the prepare that gives the safety here,it
    > > > is the use of bind variables (the '?'). IIRC, you can use bind variables
    > > > without prepare; certainly, you can create unsafe statements *with*
    > > > prepare by interpolating variables straight into the prepared statement
    > > > as your original example did.

    >
    > > how to use bind variables without prepare ? i would like to know...

    >
    > First, please start quoting properly. This is not the first time
    > you've been asked. Post your reply *below* what you are replying to.
    >
    > Second, read the DBI module's documentation. Take a look at, for
    > example, the do() method:
    >
    > my $sql = "DELETE FROM users WHERE user_name = ?";
    > $dbh->do($sql, undef, 'Mr. Itty');
    >
    > Paul Lalli


    Thanks...
     
    howa, Jun 5, 2006
    #12
  13. Guest

    "Tsz Ming Wong" <> wrote:
    > okay, such as :
    >
    > my $username = "tom' ; DELETE FROM users";
    >
    > my $sqlstr = "SELECT * FROM users WHERE name = '$username'";
    >
    > How to re-produce this hack using prepare statement?


    There is no hack to reproduce. There is no DBI, not statement (prepared
    or otherwise) and no execution. There is no hack. All you have is a
    string. A string is not a hack.

    Furthermore, if I try to guess what you mean by filling in the missing
    execution code, it is still isn't a hack because I get an SQL syntax error.

    my $db = DBI->connect("DBI:mysql::xxxxx","x", "xxxx",
    { RaiseError => 1 }) ;
    $db->do( "use xxx" );
    my $username = "tom' ;
    DELETE FROM users"; my $sqlstr = "SELECT * FROM users WHERE name =
    '$username'";
    $db->do($sqlstr); ##This is
    line 6

    DBD::mysql::db do failed: You have an error in your SQL syntax near ';
    DELETE FROM users'' at line 1 at test_dbi.pl line 6

    Xho

    --
    -------------------- http://NewsReader.Com/ --------------------
    Usenet Newsgroup Service $9.95/Month 30GB
     
    , Jun 5, 2006
    #13
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. poppy

    SQL Injection Attacks

    poppy, Nov 2, 2004, in forum: ASP .Net
    Replies:
    4
    Views:
    426
    Scott Allen
    Nov 3, 2004
  2. Darrel
    Replies:
    9
    Views:
    3,653
    Steve C. Orr [MVP, MCSD]
    Nov 11, 2004
  3. Steve
    Replies:
    4
    Views:
    397
    James Willmore
    Nov 28, 2003
  4. howa
    Replies:
    2
    Views:
    134
    Joost Diepenmaat
    Feb 25, 2008
  5. Replies:
    7
    Views:
    173
Loading...

Share This Page