Escaping single quotes with sql

Discussion in 'Perl Misc' started by Wilco van der Veer, Jun 3, 2004.

  1. Goodday,

    I am trying to put values that may contain a ' (single quote) into a
    sql database but it keeps crashing when the value contains a '. I have
    tried using the quote function:

    $string = "'s avond's";
    my $clean_string = $dbh->quote($string);

    But even then it crashes whenever a ' appears.

    Does anyone have a solution for this?

    Thanks,

    Wilco van der Veer
     
    Wilco van der Veer, Jun 3, 2004
    #1
    1. Advertising

  2. Wilco van der Veer

    Jack Challen Guest

    Wilco van der Veer wrote:
    > I am trying to put values that may contain a ' (single quote) into a
    > sql database but it keeps crashing when the value contains a '. I have
    > tried using the quote function:
    >
    > $string = "'s avond's";
    > my $clean_string = $dbh->quote($string);



    What happens if you try using placeholders (untested code ahead)?

    my $query = "INSERT INTO foo VALUES(?)";
    my $sth = $dbh->prepare($query);
    $sth->execute($string);

    Cheers
    jack
     
    Jack Challen, Jun 3, 2004
    #2
    1. Advertising

  3. Wilco van der Veer

    J. Gleixner Guest

    Wilco van der Veer wrote:
    > Goodday,
    >
    > I am trying to put values that may contain a ' (single quote) into a
    > sql database but it keeps crashing when the value contains a '. I have
    > tried using the quote function:
    >
    > $string = "'s avond's";
    > my $clean_string = $dbh->quote($string);
    >
    > But even then it crashes whenever a ' appears.
    >
    > Does anyone have a solution for this?


    That's one way to do it. It will work, depending on how you're using it.
    Show us your code where you use $clean_string to do your insert.
     
    J. Gleixner, Jun 3, 2004
    #3
  4. Wilco van der Veer

    Guest

    (Wilco van der Veer) wrote:

    > Goodday,
    >
    > I am trying to put values that may contain a ' (single quote) into a
    > sql database but it keeps crashing when the value contains a '. I have
    > tried using the quote function:



    First off, "Use bind variables and place holders" is the best answer to
    most of the DBI questions posted here. Yours is no exception. Use place
    holders and bind variables.


    > $string = "'s avond's";
    > my $clean_string = $dbh->quote($string);
    >
    > But even then it crashes whenever a ' appears.


    What do mean by "crashes"? Perl produces error messages for your
    benefit. Do you not think that we should at least look at them?

    > Does anyone have a solution for this?


    Yes.

    Xho

    --
    -------------------- http://NewsReader.Com/ --------------------
    Usenet Newsgroup Service $9.95/Month 30GB
     
    , Jun 3, 2004
    #4
  5. "J. Gleixner" <> wrote in message
    news:aMHvc.13$...
    >
    > That's one way to do it. It will work, depending on how you're using it.
    > Show us your code where you use $clean_string to do your insert.


    #!/usr/bin/perl

    # load module
    use DBI();

    # connect
    my $dbh = DBI->connect("DBI:pgPP:database=honnepon;host=$hostname", "$user",
    "$pass", {'RaiseError' => 1});

    $string = "'s avond's";
    my $clean_string = $dbh->quote($string);

    my $sth = $dbh->prepare("INSERT INTO files (filename) VALUES ('$string');");
    $sth->execute();

    # clean up
    $dbh->disconnect();
     
    Wilco van der Veer, Jun 3, 2004
    #5
  6. Wilco van der Veer

    Paul Lalli Guest

    On Thu, 3 Jun 2004, Wilco van der Veer wrote:

    > $string = "'s avond's";
    > my $clean_string = $dbh->quote($string);
    >
    > my $sth = $dbh->prepare("INSERT INTO files (filename) VALUES ('$string');");


    quote() doesn't modify its argument, it returns a new string. $string is
    exactly what it was before the call to quote(). You should be using
    $clean_string in the SQL statement.

    However, the 'correct' answer is still to be using place holders, as
    otehrs in this thread have said.

    read perldoc DBI for more info.

    Paul Lalli
     
    Paul Lalli, Jun 3, 2004
    #6
  7. <> wrote in message
    news:20040603115650.571$...
    >
    > > $string = "'s avond's";
    > > my $clean_string = $dbh->quote($string);
    > >
    > > But even then it crashes whenever a ' appears.

    >
    > What do mean by "crashes"? Perl produces error messages for your
    > benefit. Do you not think that we should at least look at them?


    Errorcode:

    DBD::pgPP::st execute failed: ERROR: parser: parse error at or near "s" at
    character 40
    DBD::pgPP::st execute failed: ERROR: parser: parse error at or near "s" at
    character 40
     
    Wilco van der Veer, Jun 3, 2004
    #7
  8. Wilco van der Veer <> wrote:

    > #!/usr/bin/perl


    No strictures or warnings? Or did you just cut them from your post?

    > # load module
    > use DBI();


    DBI doesn't export anything, so the parentheses are unnecessary.

    >
    > # connect
    > my $dbh =
    > DBI->connect("DBI:pgPP:database=honnepon;host=$hostname", "$user",
    > "$pass", {'RaiseError' => 1});


    You don't need quotes around $user and $pass. See
    perldoc -q quoting

    > $string = "'s avond's";
    > my $clean_string = $dbh->quote($string);


    This line is not necessary if you use placeholders, as at least two
    people suggested. See below.

    > my $sth = $dbh->prepare("INSERT INTO files (filename) VALUES
    > ('$string');");
    > $sth->execute();


    Better written as (rearranged to fit into an 80-character line)

    my $sth = $dbh->prepare(
    'INSERT INTO files (filename) VALUES (?)'
    );
    $sth->execute($string);

    > # clean up
    > $dbh->disconnect();
     
    David K. Wall, Jun 3, 2004
    #8
  9. "David K. Wall" <> wrote in message
    news:Xns94FD7CE87920dkwwashere@216.168.3.30...
    > Wilco van der Veer <> wrote:
    > Better written as (rearranged to fit into an 80-character line)
    >
    > my $sth = $dbh->prepare(
    > 'INSERT INTO files (filename) VALUES (?)'
    > );
    > $sth->execute($string);
    >



    Great, that works!

    Thanks alot for the help everbody!
     
    Wilco van der Veer, Jun 3, 2004
    #9
  10. Wilco van der Veer

    Keith Keller Guest

    -----BEGIN PGP SIGNED MESSAGE-----
    Hash: SHA1

    On 2004-06-03, Wilco van der Veer <> wrote:
    >
    > #!/usr/bin/perl


    Is it safe to assume that you have

    use strict;
    use warnings;

    in your code?

    [snip DBI code]

    > $string = "'s avond's";
    > my $clean_string = $dbh->quote($string);
    >
    > my $sth = $dbh->prepare("INSERT INTO files (filename) VALUES ('$string');");

    ^^^^^^^
    Did you mean to use the unquoted string? :)

    Even so, if you read perldoc DBI (you did that, right?), and look for the
    description of the quote method, you'll see that $clean_string should
    probably already have the outside quotes. You'll probably want

    my $sth = $dbh->prepare("INSERT INTO files (filename) VALUES ($clean_string)");

    Or, as someone else mentioned, just use placeholders, which will allow
    DBI and DBD::pgPP to figure out all the quoting for you.

    - --keith

    - --
    -francisco.ca.us
    (try just my userid to email me)
    AOLSFAQ=http://wombat.san-francisco.ca.us/cgi-bin/fom

    -----BEGIN PGP SIGNATURE-----
    Version: GnuPG v1.2.3 (GNU/Linux)

    iD8DBQFAv1ChhVcNCxZ5ID8RAomXAJ9QZR9q6c6i5sHNnbOCAUFoKbybTACeJ1xS
    akvli8piiNRIfYmA4Ixu6y8=
    =K3FM
    -----END PGP SIGNATURE-----
     
    Keith Keller, Jun 3, 2004
    #10
  11. Wilco van der Veer

    Ben Morrow Guest

    Quoth "David K. Wall" <>:
    > > my $dbh =
    > > DBI->connect("DBI:pgPP:database=honnepon;host=$hostname", "$user",
    > > "$pass", {'RaiseError' => 1});

    >
    > You don't need quotes around $user and $pass. See
    > perldoc -q quoting


    Nor around 'RaiseError'.=> autoquotes its left argument if it matches
    /^\w+$/.

    Ben

    --
    For the last month, a large number of PSNs in the Arpa[Inter-]net have been
    reporting symptoms of congestion ... These reports have been accompanied by an
    increasing number of user complaints ... As of June,... the Arpanet contained
    47 nodes and 63 links. [ftp://rtfm.mit.edu/pub/arpaprob.txt] *
     
    Ben Morrow, Jun 3, 2004
    #11
  12. Wilco van der Veer

    Guest

    writes:
    > (Wilco van der Veer) wrote:


    > > I am trying to put values that may contain a ' (single quote) into
    > > a sql database but it keeps crashing when the value contains a
    > > '. I have tried using the quote function:


    > First off, "Use bind variables and place holders" is the best answer
    > to most of the DBI questions posted here. Yours is no exception.
    > Use place holders and bind variables.


    If available.

    Not so much with DBD::Sybase and FreeTDS pointing at SQL Server.

    Can't speak for the OP, but in my case, I'm stuck playing
    the quote game, unpleasant as it is.

    FWIW. Annoying when building DBD::Sybase - certain tests fail.



    --
    Plain Bread alone for e-mail, thanks. The rest gets trashed.
    No HTML in E-Mail! -- http://www.expita.com/nomime.html
    Are you posting responses that are easy for others to follow?
    http://www.greenend.org.uk/rjk/2000/06/14/quoting
     
    , Jun 3, 2004
    #12
  13. Ben Morrow <> wrote:

    > Quoth "David K. Wall" <>:
    >> > my $dbh =
    >> > DBI->connect("DBI:pgPP:database=honnepon;host=$hostname",
    >> > "$user", "$pass", {'RaiseError' => 1});

    >>
    >> You don't need quotes around $user and $pass. See
    >> perldoc -q quoting

    >
    > Nor around 'RaiseError'.=> autoquotes its left argument if it
    > matches /^\w+$/.


    Thanks. Somehow I missed seeing that one.
     
    David K. Wall, Jun 3, 2004
    #13
  14. David K. Wall <> wrote:
    > Wilco van der Veer <> wrote:


    >> use DBI();

    >
    > DBI doesn't export anything, so the parentheses are unnecessary.



    But if the parenthesis are not there, then you have to go find
    out if DBI exports anything or not.

    With parens you know right away that nothing is being imported
    without having to go do any research.


    I'd call using those parens the "good kind" of Lazy. :)


    --
    Tad McClellan SGML consulting
    Perl programming
    Fort Worth, Texas
     
    Tad McClellan, Jun 3, 2004
    #14
  15. Tad McClellan <> wrote:

    > David K. Wall <> wrote:
    >> Wilco van der Veer <> wrote:


    >>> use DBI();


    >> DBI doesn't export anything, so the parentheses are unnecessary.


    > But if the parenthesis are not there, then you have to go find
    > out if DBI exports anything or not.
    >
    > With parens you know right away that nothing is being imported
    > without having to go do any research.
    >
    >
    > I'd call using those parens the "good kind" of Lazy. :)


    I hadn't looked at it that way. I'll have to keep that in mind for my own
    use.
     
    David K. Wall, Jun 4, 2004
    #15
  16. Wilco van der Veer

    Guest

    Tad McClellan <> wrote in message news:<>...
    > David K. Wall <> wrote:
    > > Wilco van der Veer <> wrote:

    >
    > >> use DBI();

    > >
    > > DBI doesn't export anything, so the parentheses are unnecessary.

    >
    >
    > But if the parenthesis are not there, then you have to go find
    > out if DBI exports anything or not.
    >
    > With parens you know right away that nothing is being imported
    > without having to go do any research.
    >
    >
    > I'd call using those parens the "good kind" of Lazy. :)


    I have to disagree. Using empty parens on use is like using & on a
    function call. It is a way to tell Perl that you want to suppress
    part of the normal functionality. It should oly be used on special
    occasions. For this reason whenever I see it I am inclined to go off
    and do research to figure out what it is that is being avioded.

    I'd always stick to:

    use DBI;

    or

    require DBI;
     
    , Jun 5, 2004
    #16
    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. Chris
    Replies:
    1
    Views:
    13,661
    Oisin
    Mar 24, 2006
  2. Lawrence Tierney

    Multiline quotes - escaping quotes - et al

    Lawrence Tierney, Dec 24, 2003, in forum: Java
    Replies:
    3
    Views:
    4,504
    Andrew Thompson
    Dec 24, 2003
  3. Francis Hwang
    Replies:
    2
    Views:
    355
    Brian Candler
    Oct 21, 2004
  4. lists
    Replies:
    3
    Views:
    257
    lists
    Oct 21, 2005
  5. Replies:
    7
    Views:
    179
    Thomas 'PointedEars' Lahn
    May 17, 2005
Loading...

Share This Page