pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedescape single quote in INSERT command
8+ messages / 7 participants
[nested] [flat]
* escape single quote in INSERT command
@ 2002-11-22 13:00 Hunter <dave_h4@yahoo.com>
2002-11-22 18:38 ` Re: escape single quote in INSERT command Jeff Boes <jboes@nexcerpt.com>
2002-11-22 21:50 ` Re: escape single quote in INSERT command mark carew <markcarew@bigpond.com>
2002-11-22 21:52 ` Re: escape single quote in INSERT command mark carew <markcarew@bigpond.com>
2002-11-26 18:31 ` Re: escape single quote in INSERT command mallah@trade-india.com
2002-11-26 18:47 ` Re: escape single quote in INSERT command Dan Langille <dan@langille.org>
0 siblings, 5 replies; 8+ messages in thread
From: dave_h4@yahoo.com @ 2002-11-22 13:00 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: escape single quote in INSERT command
2002-11-22 13:00 escape single quote in INSERT command Hunter <dave_h4@yahoo.com>
@ 2002-11-22 18:38 ` Jeff Boes <jboes@nexcerpt.com>
4 siblings, 0 replies; 8+ messages in thread
From: Jeff Boes @ 2002-11-22 18:38 UTC (permalink / raw)
To: pgsql-sql
On Fri, 22 Nov 2002 08:00:01 -0500, Hunter wrote:
> 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.
Are you using the DBI interface? That's the most straightforward way to
accomplish this. Something like this:
use strict;
use DBI;
my $dbh = DBI->new(...see docs for details...);
$dbh->do(q|INSERT INTO mytable (col1) VALUES (?)|, undef,
q|O'Malley|) or die $DBI::errstr;
$dbh->commit;
By using the '?' placeholder format for statements, you can pass in any
data without having to worry about quoting.
--
Jeff Boes vox 616.226.9550 ext 24
Database Engineer fax 616.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: escape single quote in INSERT command
2002-11-22 13:00 escape single quote in INSERT command Hunter <dave_h4@yahoo.com>
@ 2002-11-22 21:50 ` mark carew <markcarew@bigpond.com>
4 siblings, 0 replies; 8+ messages in thread
From: mark carew @ 2002-11-22 21:50 UTC (permalink / raw)
To: pgsql-sql
Hi Hunter,
From my xbase++ program suite, sqlcontext class.
*
cValue := strtran(cValue,['],[\'])
*
Its called masquarading, simply replace the single quote with back_slash
+ single quote.
Regards
Mark Carew
Brisbane Australia
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: escape single quote in INSERT command
2002-11-22 13:00 escape single quote in INSERT command Hunter <dave_h4@yahoo.com>
@ 2002-11-22 21:52 ` mark carew <markcarew@bigpond.com>
4 siblings, 0 replies; 8+ messages in thread
From: mark carew @ 2002-11-22 21:52 UTC (permalink / raw)
To: pgsql-sql
Woops should have been masquerading
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: escape single quote in INSERT command
2002-11-22 13:00 escape single quote in INSERT command Hunter <dave_h4@yahoo.com>
@ 2002-11-26 18:31 ` mallah@trade-india.com
2002-11-26 18:23 ` Re: escape single quote in INSERT command Thomas Good <tomg@q8.nrnet.org>
4 siblings, 1 reply; 8+ messages in thread
From: mallah@trade-india.com @ 2002-11-26 18:31 UTC (permalink / raw)
To: dave_h4@yahoo.com; +Cc: pgsql-sql
> 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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: escape single quote in INSERT command
2002-11-22 13:00 escape single quote in INSERT command Hunter <dave_h4@yahoo.com>
2002-11-26 18:31 ` Re: escape single quote in INSERT command mallah@trade-india.com
@ 2002-11-26 18:23 ` Thomas Good <tomg@q8.nrnet.org>
0 siblings, 0 replies; 8+ messages in thread
From: Thomas Good @ 2002-11-26 18:23 UTC (permalink / raw)
To: mallah@trade-india.com; +Cc: dave_h4@yahoo.com; pgsql-sql
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! --
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: escape single quote in INSERT command
2002-11-22 13:00 escape single quote in INSERT command Hunter <dave_h4@yahoo.com>
@ 2002-11-26 18:47 ` Dan Langille <dan@langille.org>
4 siblings, 0 replies; 8+ messages in thread
From: Dan Langille @ 2002-11-26 18:47 UTC (permalink / raw)
To: dave_h4@yahoo.com; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: escape single quote in INSERT command
@ 2002-11-26 18:41 Thomas Good <tomg@sqlclinic.net>
0 siblings, 0 replies; 8+ messages in thread
From: Thomas Good @ 2002-11-26 18:41 UTC (permalink / raw)
To: pgsql-sql
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! --
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2002-11-26 18:47 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-22 13:00 escape single quote in INSERT command Hunter <dave_h4@yahoo.com>
2002-11-22 18:38 ` Jeff Boes <jboes@nexcerpt.com>
2002-11-22 21:50 ` mark carew <markcarew@bigpond.com>
2002-11-22 21:52 ` mark carew <markcarew@bigpond.com>
2002-11-26 18:31 ` mallah@trade-india.com
2002-11-26 18:23 ` Thomas Good <tomg@q8.nrnet.org>
2002-11-26 18:47 ` Dan Langille <dan@langille.org>
2002-11-26 18:41 Re: escape single quote in INSERT command Thomas Good <tomg@sqlclinic.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox