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

Hi Monks

I am inserting form data into sql, but whenever the $variable contains double quotes the entry does not get saved into sql I have done a =~ s/\"/\\"/; which fixes it, but are there better ways to do thise without doing a s// ?

Any advice appreciated?

Replies are listed 'Best First'.
Re: SQL Quoting
by Kanji (Parson) on Jan 28, 2002 at 19:01 UTC

    DBI's quote method will do this for you ...

    quote
        $sql = $dbh->quote($value);
        $sql = $dbh->quote($value, $data_type);

    Quote a string literal for use as a literal value in an SQL statement, by escaping any special characters (such as quotation marks) contained within the string and adding the required type of outer quotation marks.

    And if you use placeholders in your do and prepares, the corresponding arguments you pass to execute will automatically be quoted for you.

        --k.


[reply]
Re: SQL Quoting
by Masem (Monsignor) on Jan 28, 2002 at 19:07 UTC
    Are you using DBI? If so, you should use placeholders to specify what gets put into the DB. Before being added, all questionable characters, including single and double quotes, are appropriate escaped to avoid problems. 
    use DBI;
my $dbh = DBI->connect( ... );
my $sth = $dbh->prepare( "INSERT INTO table VALUES ( ?, ?, ? )" ) or d
+ie $DBI->errstr;
$sth->execute( $name, $address, $comment ) or die $DBI->errstr;

    [download]
    In the case above, the SQL that will be processed is: 
    INSERT INTO table VALUES ( $name, $address, $comment )

    [download]
    But with $name, $address, and $comment appropriate escaped to avoid problems with quoted characters.

    -----------------------------------------------------
    Dr. Michael K. Neylon - mneylon-pm@masemware.com || "You've left the lens cap of your mind on again, Pinky" - The Brain
    "I can see my house from here!"
    It's not what you know, but knowing how to find it if you don't know that's important

[reply]
[d/l]
[select]
      And the biggest bonus of all: Most modern DB's will cache the statement handle for you so that you can use the same SQL again and again, and it won't have to go through the SQL parsing overhead (not that there's much overhead, but it's still a bonus). Plus you can use $dbh->prepare_cached() if the underlying database doesn't do that for you.

      In summary, always use placeholders. There's simply no reason not to.

[reply]
[d/l]
Re: SQL Quoting
by busunsl (Vicar) on Jan 28, 2002 at 19:03 UTC
    Using placeholders might help.

    Some RDBMS don't like double quotes inside of character strings, so your way might be the only one.

    Give us some more information about the database and the error messages and show us a piece of code.

[reply]
Re: SQL Quoting
by Purdy (Hermit) on Jan 29, 2002 at 02:44 UTC
    Just wanted to add that if you just need to 'do' a command instead of preparing/executing it (useful if you are going to execute the same sql statement more than once), it also has support for placeholders (as the previous posters mention, placeholders rock!). 

    # Note the 'undef' in between the sql statement and the
# bound variables.
$rv = $dbh->do( "UPDATE table SET column=? WHERE column=?",
 undef, "New Value!!", $variable );

    [download]

    Jason

[reply]
[d/l]

Back to Seekers of Perl Wisdom