Thread: Problem with inserting newlines and ' characters

Hi,

This may actually be more of a limitation with the DBD::Pg perl driver.
I'm not sure.

Basically I'm using DBD::Pg in a perl script which inserts data into a
very simple table.

The statement I'm using is thus :

my($sth) = $dbh->prepare("insert into maintenance (sno, date, data) values
('$sno', '$date', '$tmpstring')");

(all on one line of course...)

My problem is this. $tmpstring may well contain embedded newlines or '
characters (these are the two characters I've had problems with so far).

I don't want to go down the route of rewriting those characters into
something 'unique' and decoding whenever I extract information from the
table. That's always struck me as a bit of a kludge.

So, is it a problem with postgresql, or DBD::Pg, or (most likely) what I'm
doing with them... ?

Any help much appreciated.

Cheers,

Al

Well, you will have to escape the single quotes but you shouldn't have to
escape the newlines, I'm inserting some chunks of text that have all sorts
of newline characters and I'm not escaping them, it seems to work fine..

-Mitch

----- Original Message -----
From: "Alan Horn" <ahorn@inktomi.com>
To: <pgsql-general@postgresql.org>
Sent: Monday, July 24, 2000 10:38 AM
Subject: [GENERAL] Problem with inserting newlines and ' characters


>
> Hi,
>
> This may actually be more of a limitation with the DBD::Pg perl driver.
> I'm not sure.
>
> Basically I'm using DBD::Pg in a perl script which inserts data into a
> very simple table.
>
> The statement I'm using is thus :
>
> my($sth) = $dbh->prepare("insert into maintenance (sno, date, data) values
> ('$sno', '$date', '$tmpstring')");
>
> (all on one line of course...)
>
> My problem is this. $tmpstring may well contain embedded newlines or '
> characters (these are the two characters I've had problems with so far).
>
> I don't want to go down the route of rewriting those characters into
> something 'unique' and decoding whenever I extract information from the
> table. That's always struck me as a bit of a kludge.
>
> So, is it a problem with postgresql, or DBD::Pg, or (most likely) what I'm
> doing with them... ?
>
> Any help much appreciated.
>
> Cheers,
>
> Al
>
>
>
>
>
>
>

At 14:38 24/07/00 +0000, Alan Horn wrote:
>
>Hi,
>
>This may actually be more of a limitation with the DBD::Pg perl driver.
>I'm not sure.
>
>Basically I'm using DBD::Pg in a perl script which inserts data into a
>very simple table.
>
>The statement I'm using is thus :
>
>my($sth) = $dbh->prepare("insert into maintenance (sno, date, data) values
>('$sno', '$date', '$tmpstring')");
>
>(all on one line of course...)
>
>My problem is this. $tmpstring may well contain embedded newlines or '
>characters (these are the two characters I've had problems with so far).

I use embedded chars all the time, but with placeholders in the query. Try:

   my($sth) = $dbh->prepare("insert into maintenance (sno, date, data) values
            (?, ?, ?)");
then

    if (!$sth->execute($sno, $date, $tmpstring))
    die_horribly....

this way you'll get the value out of preparing the statement. You might
also want to use 'prepare_cached'.

I don't know if your original query should work, but it will have all sorts
of problems if, eg, you have a "'" in one of the strings.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Alan Horn wrote:
...
> The statement I'm using is thus :
>
> my($sth) = $dbh->prepare("insert into maintenance (sno, date, data) values
> ('$sno', '$date', '$tmpstring')");
>
> (all on one line of course...)
>
> My problem is this. $tmpstring may well contain embedded newlines or '
> characters (these are the two characters I've had problems with so far).
...

The method you're looking for is $dbh->quote.  You could use it like:

$sth = $dbh->prepare("insert into maintenance (sno, date, data)
                        values ($dbh->quote($sno),
                                $dbh->quote($date),
                                $dbh->quote($tmpstring))");

Hope that helps.

Regards,
Kyle