Thread: escape single quote in INSERT command

Hi Group -

I have a perl application for a registration form. I'd like to put
escape characters in my insert command to accommodate for '
(i.e. O'Brien, O'Malley, etc). I've tired double quotes, single
quotes, back tick, forward ticks, curly bracket, round brackets - no
success.


Thanks, dave

Hunter wrote:
> Hi Group -
>
> I have a perl application for a registration form. I'd like to put
> escape characters in my insert command to accommodate for '
> (i.e. O'Brien, O'Malley, etc). I've tired double quotes, single
> quotes, back tick, forward ticks, curly bracket, round brackets - no
> success.

The SQL standard in literals is to quote with another single quote (like
'O''Brien'). Not sure why that didn't work for you. But PostgreSQL also
escapes with backslashes ('O\'Brien'), so they also must be escaped
('c:\\winnt'). SQL-92 doesn't. So there's no database-independent way to
quote things 100% correctly.

So don't quote it manually. Either do (preferred)

     my $sth = $dbh->prepare(qq{insert into mytable values (?, ?, ?)});
     $sth->execute($a, $b, $c);

or

     $dbh->do(sprintf("insert into mytable values (%s, %s, %s)",
                      $dbh->quote($a), $dbh->quote($b),
                      $dbh->quote($c)));

The first way is better because:

- you don't have to explicitly think about quoting; it takes care of it
- it can use server-side prepared statements for more goodness

The second way, though, at least quotes things correctly. You need to
use $dbh->quote() to do that because quoting is database-specific, as I
mentioned above.

Both ways map Perl undef -> null for you.

Scott

If you're using DBD::Pg, just use ? placeholders without any quotes and let
it do the escaping for you:

$sth = $dbh->prepare("insert into customer (name) values (?)");
$sth->execute($name);

Makes the code a little more portable too. Hope this helps,

Wes




dave_h4@yahoo.com (Hunter)@postgresql.org on 11/22/2002 08:00:31 AM

Sent by:    pgsql-general-owner@postgresql.org


To:    pgsql-general@postgresql.org
cc:
Subject:    [GENERAL] escape single quote in INSERT command


Hi Group -

I have a perl application for a registration form. I'd like to put
escape characters in my insert command to accommodate for '
(i.e. O'Brien, O'Malley, etc). I've tired double quotes, single
quotes, back tick, forward ticks, curly bracket, round brackets - no
success.


Thanks, dave

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org