Обсуждение: "'" in SQL INSERT statement

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

Markus Wagner wrote: >Hi, > >I have some data that I wish to transfer into a database using perl/DBI. >Some of the data
arestrings containing the apostrophe "'" which I use >as string delimiter. > >How can I put these into my database
usingthe 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 text2 | 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 text2 | John's text3 | 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
deedand in truth."                                                     I John 3:18 
 

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

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"

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

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
> 

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];
returnundef 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
--------------------------------------------------------------------------