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
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! --