Обсуждение: 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

Woops should have been  masquerading

> Hi Group -
>
> I have a perl application for a registration form.

Same Here,

Why dont' you use prepare and execute  in case you are using DBI
same program is like this.

$dbh = DBI -> connect ( "......");
$sth = $dbh -> prepare("insert into tab (a,b) values (?,?)");
$sth -> execute($a , $b );
$sth -> finish();
$dbh -> commit();
$dbh -> disconnect();


regds
mallah.


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



-----------------------------------------
Get your free web based email at trade-india.com.  "India's Leading B2B eMarketplace.!"
http://www.trade-india.com/

On 27 Nov 2002 at 0:01, mallah@trade-india.com wrote:

> > Hi Group -
> >
> > I have a perl application for a registration form.
> 
> Same Here,
> 
> Why dont' you use prepare and execute  in case you are using DBI
> same program is like this.
> 
> $dbh = DBI -> connect ( "......");
> $sth = $dbh -> prepare("insert into tab (a,b) values (?,?)");
> $sth -> execute($a , $b );
> $sth -> finish();
> $dbh -> commit();
> $dbh -> disconnect();

IIRC, there is a dbi->quote() function as well.  That should properly 
escape anything.
-- 
Dan Langille : http://www.langille.org/

On Wed, 27 Nov 2002 mallah@trade-india.com wrote:

> Why dont' you use prepare and execute  in case you are using DBI
> same program is like this.
>
> $dbh = DBI -> connect ( "......");
> $sth = $dbh -> prepare("insert into tab (a,b) values (?,?)");
> $sth -> execute($a , $b );
> $sth -> finish();
> $dbh -> commit();
> $dbh -> disconnect();

> I'd like to put escape characters in my
> > insert command to accommodate for '

$dbh->quote() will do the escaping for DBI but be careful with dates
as the variable binding does not always behave as expected.

You can esc the single with another single, ala ANSI SQL: ''
This works in Oracle, PG and MySQL for sure.

In perl:  $name =~ s/\'/\'\'/g;
$query = qq |insert into x values ('$name')|;
and so on...

Now, can some kind soul tell me how to do an 'insert into x select y;'
where x is a numeric(19,2) and y is a money type???
-----------------------------------------------------------------------
Thomas Good                                  e-mail: tomg@sqlclinic.net
Programmer/Analyst                           phone:   (+1) 718.818.5528
Residential Services                         fax:     (+1) 718.818.5056
Behavioral Health Services, SVCMC-NY         mobile:  (+1) 917.282.7359
   --            Geistiges Eigentum ist Diebstahl!              --

On Wed, 27 Nov 2002 mallah@trade-india.com wrote:

> Why dont' you use prepare and execute  in case you are using DBI
> same program is like this.
>
> $dbh = DBI -> connect ( "......");
> $sth = $dbh -> prepare("insert into tab (a,b) values (?,?)");
> $sth -> execute($a , $b );
> $sth -> finish();
> $dbh -> commit();
> $dbh -> disconnect();

> I'd like to put escape characters in my
> > insert command to accommodate for '

$dbh->quote() will do the escaping for DBI but be careful with dates
as the variable binding does not always behave as expected.

You can esc the single with another single, ala ANSI SQL: ''
This works in Oracle, PG and MySQL for sure.

In perl:  $name =~ s/\'/\'\'/g;
$query = qq |insert into x values ('$name')|;
and so on...

Now, can some kind soul tell me how to do an 'insert into x select y;'
where x is a numeric(19,2) and y is a money type???
-----------------------------------------------------------------------
Thomas Good                                  e-mail: tomg@sqlclinic.net
Programmer/Analyst                           phone:   (+1) 718.818.5528
Residential Services                         fax:     (+1) 718.818.5056
Behavioral Health Services, SVCMC-NY         mobile:  (+1) 917.282.7359
   --            Geistiges Eigentum ist Diebstahl!              --