pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
"'" in SQL INSERT statement
7+ messages / 7 participants
[nested] [flat]

* "'" in SQL INSERT statement
@ 2001-01-25 10:12 Markus Wagner <wagner@imsd.uni-mainz.de>
  2001-01-25 10:19 ` Re: "'" in SQL INSERT statement Oliver Elphick <olly@lfix.co.uk>
  2001-01-25 10:30 ` Re: "'" in SQL INSERT statement Alessio Bragadini <alessio@albourne.com>
  2001-01-25 10:49 ` Re: "'" in SQL INSERT statement Brett W. McCoy <bmccoy@chapelperilous.net>
  2001-01-25 17:42 ` Re: "'" in SQL INSERT statement Albert REINER <areiner@tph.tuwien.ac.at>
  2001-01-25 18:31 ` Re: "'" in SQL INSERT statement Jie Liang <jliang@ipinc.com>
  0 siblings, 5 replies; 7+ messages in thread

From: Markus Wagner @ 2001-01-25 10:12 UTC (permalink / raw)
  To: pgsql-sql

Hi,

I have some data that I wish to transfer into a database using perl/DBI.
Some of the data are strings containing the apostrophe "'" which I use
as string delimiter.

How can I put these into my database using the INSERT statement?

Thanks,

Markus



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: "'" in SQL INSERT statement
  2001-01-25 10:12 "'" in SQL INSERT statement Markus Wagner <wagner@imsd.uni-mainz.de>
@ 2001-01-25 10:19 ` Oliver Elphick <olly@lfix.co.uk>
  4 siblings, 0 replies; 7+ messages in thread

From: Oliver Elphick @ 2001-01-25 10:19 UTC (permalink / raw)
  To: Markus Wagner <wagner@imsd.uni-mainz.de>; +Cc: pgsql-sql

Markus Wagner wrote:
  >Hi,
  >
  >I have some data that I wish to transfer into a database using perl/DBI.
  >Some of the data are strings containing the apostrophe "'" which I use
  >as string delimiter.
  >
  >How can I put these into my database using the INSERT statement?

Escape the apostrophe with another apostrophe or a backslash:

junk=# insert into a (b) values ('John''s text');
INSERT 6815936 1
junk=# select * from a;
 a |      b      
---+-------------
 1 | some text
 2 | John's text
(2 rows)

junk=# insert into a (b) values ('Fred\'s text');
INSERT 6815937 1
junk=# select * from a;
 a |      b      
---+-------------
 1 | some text
 2 | John's text
 3 | Fred's text
(3 rows)

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "My little children, let us not love in word, neither 
      in tongue; but in deed and in truth."          
                                            I John 3:18 





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: "'" in SQL INSERT statement
  2001-01-25 10:12 "'" in SQL INSERT statement Markus Wagner <wagner@imsd.uni-mainz.de>
@ 2001-01-25 10:30 ` Alessio Bragadini <alessio@albourne.com>
  2001-01-25 18:03   ` Re: "'" in SQL INSERT statement Dan Lyke <danlyke@flutterby.com>
  4 siblings, 1 reply; 7+ messages in thread

From: Alessio Bragadini @ 2001-01-25 10:30 UTC (permalink / raw)
  To: Markus Wagner <wagner@imsd.uni-mainz.de>; +Cc: pgsql-sql

Markus Wagner wrote:

> I have some data that I wish to transfer into a database using perl/DBI.

If you use Perl DBI you should issue statements like
$dbh->do ('INSERT INTO table (field1, field2) VALUES (?,?)',
	undef, $value1, $value2);

This binding takes care of quoting and escapes all characters that may
cause problems in the database backed (e.g. "that's" becomes "that''s"
etc.)

There is a DBI mailing list where you can find more info and support:
see <http://www.isc.org/dbi-lists.html;

-- 
Alessio F. Bragadini		alessio@albourne.com
APL Financial Services		http://village.albourne.com
Nicosia, Cyprus		 	phone: +357-2-755750

"It is more complicated than you think"
		-- The Eighth Networking Truth from RFC 1925



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: "'" in SQL INSERT statement
  2001-01-25 10:12 "'" in SQL INSERT statement Markus Wagner <wagner@imsd.uni-mainz.de>
  2001-01-25 10:30 ` Re: "'" in SQL INSERT statement Alessio Bragadini <alessio@albourne.com>
@ 2001-01-25 18:03   ` Dan Lyke <danlyke@flutterby.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Dan Lyke @ 2001-01-25 18:03 UTC (permalink / raw)
  To: Alessio Bragadini <alessio@albourne.com>; +Cc: Markus Wagner <wagner@imsd.uni-mainz.de>; pgsql-sql

Alessio Bragadini writes:
> Markus Wagner wrote:
> > I have some data that I wish to transfer into a database using perl/DBI.
> 
> If you use Perl DBI you should issue statements like
> $dbh->do ('INSERT INTO table (field1, field2) VALUES (?,?)',
> 	undef, $value1, $value2);

$dbh->quote() also puts in the appropriate escapes, ala:

   $dbh->do('INSERT INTO TABLE (field) VALUES ('.$dbh->quote($value).')');

Dan



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: "'" in SQL INSERT statement
  2001-01-25 10:12 "'" in SQL INSERT statement Markus Wagner <wagner@imsd.uni-mainz.de>
@ 2001-01-25 10:49 ` Brett W. McCoy <bmccoy@chapelperilous.net>
  4 siblings, 0 replies; 7+ messages in thread

From: Brett W. McCoy @ 2001-01-25 10:49 UTC (permalink / raw)
  To: Markus Wagner <wagner@imsd.uni-mainz.de>; +Cc: pgsql-sql

On Thu, 25 Jan 2001, Markus Wagner wrote:

> I have some data that I wish to transfer into a database using perl/DBI.
> Some of the data are strings containing the apostrophe "'" which I use
> as string delimiter.
>
> How can I put these into my database using the INSERT statement?

You will need to escape them with the \ character.  So "Bill's Garage"
will become "Bill\'s Garage".

-- Brett
                                     http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
Romeo wasn't bilked in a day.
		-- Walt Kelly, "Ten Ever-Lovin' Blue-Eyed Years With Pogo"




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: "'" in SQL INSERT statement
  2001-01-25 10:12 "'" in SQL INSERT statement Markus Wagner <wagner@imsd.uni-mainz.de>
@ 2001-01-25 17:42 ` Albert REINER <areiner@tph.tuwien.ac.at>
  4 siblings, 0 replies; 7+ messages in thread

From: Albert REINER @ 2001-01-25 17:42 UTC (permalink / raw)
  To: pgsql-sql

Saluton!

On Thu, Jan 25, 2001 at 11:12:34AM +0100, Markus Wagner wrote:
...
> Some of the data are strings containing the apostrophe "'" which I use
> as string delimiter.
> 
> How can I put these into my database using the INSERT statement?

I always use this sub:

    #
    # This sub adapted from sub TEXT of mmusic by ChLorenz@csi.com, using
    # advice from the pgsql-novice mailing list (David Rugge, 1 Aug 1999).
    #
    # Do the necessary quoting for strings.
    #
    sub stdstr {
      my $or = $_[0];
      return undef unless (defined($or));
      $or =~ s /\\/\\\\/g;    # mmusic doesn't have this, nor does D. Rugge.
      $or =~ s /\'/\\\'/g;
      $or =~ s /\"/\\\"/g;
      $or =~ s /%/\\%/g;      # mmusic doesn't have this.
      return $or;
    }

Obviously you also need to escape the \. I no longer remember why "
and % are needed, but I certainly did have some reason then.

Albert.


-- 

--------------------------------------------------------------------------
Albert Reiner                                   <areiner@tph.tuwien.ac.at>
Deutsch       *       English       *       Esperanto       *       Latine
--------------------------------------------------------------------------



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: "'" in SQL INSERT statement
  2001-01-25 10:12 "'" in SQL INSERT statement Markus Wagner <wagner@imsd.uni-mainz.de>
@ 2001-01-25 18:31 ` Jie Liang <jliang@ipinc.com>
  4 siblings, 0 replies; 7+ messages in thread

From: Jie Liang @ 2001-01-25 18:31 UTC (permalink / raw)
  To: Markus Wagner <wagner@imsd.uni-mainz.de>; +Cc: pgsql-sql

Hi,

Using a backslash to escape it.

insert into table(field) values('what\'s that');

Jie LIANG

Internet Products Inc.

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

jliang@ipinc.com
www.ipinc.com

On Thu, 25 Jan 2001, Markus Wagner wrote:

> Hi,
> 
> I have some data that I wish to transfer into a database using perl/DBI.
> Some of the data are strings containing the apostrophe "'" which I use
> as string delimiter.
> 
> How can I put these into my database using the INSERT statement?
> 
> Thanks,
> 
> Markus
> 




^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2001-01-25 18:31 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-25 10:12 "'" in SQL INSERT statement Markus Wagner <wagner@imsd.uni-mainz.de>
2001-01-25 10:19 ` Oliver Elphick <olly@lfix.co.uk>
2001-01-25 10:30 ` Alessio Bragadini <alessio@albourne.com>
2001-01-25 18:03   ` Dan Lyke <danlyke@flutterby.com>
2001-01-25 10:49 ` Brett W. McCoy <bmccoy@chapelperilous.net>
2001-01-25 17:42 ` Albert REINER <areiner@tph.tuwien.ac.at>
2001-01-25 18:31 ` Jie Liang <jliang@ipinc.com>

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