Thread: "'" 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
--------------------------------------------------------------------------