pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
What do I need to escape in an Insert ?
5+ messages / 4 participants
[nested] [flat]

* What do I need to escape in an Insert ?
@ 1998-07-30 22:50  Daniele Orlandi <daniele@orlandi.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Daniele Orlandi @ 1998-07-30 22:50 UTC (permalink / raw)
  To: pgsql-sql@hub.org

Suppose I'm going to insert the content of a <TEXTAREA> in a table.
Obviously, there will be characters that could potentially confuse the SQL
statement. What (and how) do I need to escape the data to make it acceptable for
an Insert ?

I currently use the PHP's addlashed function, is it enought ?

Thanks for your response !

Bye!

-- 
 Daniele

-------------------------------------------------------------------------------
Why use Windows, since there is a door?
(By fachat@galileo.rhein-neckar.de, Andre Fachat)
-------------------------------------------------------------------------------
 Se telecom italia aggiungesse uno scatto al giorno ad ogni abbonato, dal
 primo Gennaio avrebbe rubato 669.891.000.000 Lire.
-------------------------------------------------------------------------------
 Daniele Orlandi - Utility Line Italia - http://www.orlandi.com
 Via Mezzera 29/A - 20030 - Seveso (MI) - Italy
-------------------------------------------------------------------------------



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

* Re: [SQL] What do I need to escape in an Insert ?
@ 1998-07-31 04:42  Dan Delaney <dionysos@dionysia.org>
  parent: Daniele Orlandi <daniele@orlandi.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Dan Delaney @ 1998-07-31 04:42 UTC (permalink / raw)
  To: Daniele Orlandi <daniele@orlandi.com>; +Cc: pgsql-sql

On Fri, 31 Jul 1998, Daniele Orlandi wrote:
> Suppose I'm going to insert the content of a <TEXTAREA> in a
> table.  Obviously, there will be characters that could potentially
> confuse the SQL statement. What (and how) do I need to escape the
> data to make it acceptable for an Insert ?  I currently use the
> PHP's addlashed function, is it enought ? 

I'm not sure if PHP's addslashes command will take care of single
quotes, I haven't tried it yet. I believe the proper escape for a
single quote in SQL is to put another single quote in front of it.
So if a person's last name is "O'Brien" it would be inserted as:

INSERT INTO personel (fname,lname) VALUES ('Michael','O''Brien');

So what I do is a Regular Expression Replace in PHP, like this:

   $lname = ereg_replace("'", "''", $lname);

That replaces every occurance of a single quote with TWO sinle
quotes, which satisfies PostgreSQL in the INSERT command. I don't
know if PostgreSQL would take "O\'Brien" or not.

Also, since I'm doing an entirely web-based interface for my
databases, when I have a textarea to go into a field, I go ahead and
store paragraph tags in the table. Like this:

     /* Replace two returns with paragraph tags */
     $description = ereg_replace("\n\n", "</P>\n\n<P>", $description);
     $notes = ereg_replace("\n\n", "</P>\n\n<P>", $notes);

     /* Replace just one return with <BR> */
     $description = ereg_replace("\n", "<BR>\n", $description);
     $notes = ereg_replace("\n", "<BR>\n", $notes);

     /* Add Paragraph tags to beginning and end */
     $description = "<P>".$description."</P>";
     $notes = "<P>".$notes."</P>";

There may be an easier way to do that, but this works great for now.
So, in the textarea on the web page, the person entering the data
can hit return once to put a <BR> tag into it, and hit return twice
to actually start a new paragraph.

 --Dan

-----------------------------------------------------------------------
 Daniel G. Delaney                    The Louisville Times Chorus
 Dionysos@Dionysia.org                   www.LouisvilleTimes.org
 www.Dionysia.org/~dionysos/          Dionysia Design
 ICQ Number: 8171285                     www.Dionysia.com/design/
-----------------------------------------------------------------------
                   I doubt, therefore I might be.





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

* Re: [SQL] What do I need to escape in an Insert ?
@ 1998-07-31 05:01  Bruce Momjian <maillist@candle.pha.pa.us>
  parent: Dan Delaney <dionysos@dionysia.org>
  0 siblings, 1 reply; 5+ messages in thread

From: Bruce Momjian @ 1998-07-31 05:01 UTC (permalink / raw)
  To: dionysos@dionysia.org; +Cc: daniele@orlandi.com; pgsql-sql

> On Fri, 31 Jul 1998, Daniele Orlandi wrote:
> > Suppose I'm going to insert the content of a <TEXTAREA> in a
> > table.  Obviously, there will be characters that could potentially
> > confuse the SQL statement. What (and how) do I need to escape the
> > data to make it acceptable for an Insert ?  I currently use the
> > PHP's addlashed function, is it enought ? 
> 
> I'm not sure if PHP's addslashes command will take care of single
> quotes, I haven't tried it yet. I believe the proper escape for a
> single quote in SQL is to put another single quote in front of it.
> So if a person's last name is "O'Brien" it would be inserted as:
> 
> INSERT INTO personel (fname,lname) VALUES ('Michael','O''Brien');
> 
> So what I do is a Regular Expression Replace in PHP, like this:
> 
>    $lname = ereg_replace("'", "''", $lname);

But this does not handle backslashes in the string.  I think the PHP
function does both. 

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)



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

* Re: [SQL] What do I need to escape in an Insert ?
@ 1998-07-31 12:26  Daniele Orlandi <daniele@orlandi.com>
  parent: Bruce Momjian <maillist@candle.pha.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Daniele Orlandi @ 1998-07-31 12:26 UTC (permalink / raw)
  To: pgsql-sql

Bruce Momjian wrote:
> 
> > I'm not sure if PHP's addslashes command will take care of single
> > quotes, I haven't tried it yet. I believe the proper escape for a
> > single quote in SQL is to put another single quote in front of it.
> > So if a person's last name is "O'Brien" it would be inserted as:
> >
> > INSERT INTO personel (fname,lname) VALUES ('Michael','O''Brien');
> >
> > So what I do is a Regular Expression Replace in PHP, like this:
> >
> >    $lname = ereg_replace("'", "''", $lname);
> 
> But this does not handle backslashes in the string.  I think the PHP
> function does both.

Looking at the source of the Bug Database at php.net, I noticed that strings are
inserted as they are, without any escaping. I still don't know where and why,
but PHP seems to escape quotes and slashes by itself when working with strings.

For example, I have the following code in my page:

$Query=sprintf("INSERT INTO bugdb (email,sdescr,ldescr,version,status) VALUES (
'%s','%s','%s','%s','Open' )",$EMail,$SDescr,$LDescr,$Version);

echo $Query;

In the textarea (ltext) I put:

\\
\n
'
"
abc

And the resulting query is:

INSERT INTO bugdb (email,sdescr,ldescr,version,status) VALUES (
'daniele@orlandi.com','Test3','\\\\ \\n \' \" abc','3.0.0','Open' )

Well, I've not yet been able to confuse PostgreSQL and everything i get from a
select is identical to what I put with the insert.

Bye!

-- 
 Daniele

-------------------------------------------------------------------------------
Why use Windows, since there is a door?
(By fachat@galileo.rhein-neckar.de, Andre Fachat)
-------------------------------------------------------------------------------
 Se telecom italia aggiungesse uno scatto al giorno ad ogni abbonato, dal
 primo Gennaio avrebbe rubato 671.633.000.000 Lire.
-------------------------------------------------------------------------------
 Daniele Orlandi - Utility Line Italia - http://www.orlandi.com
 Via Mezzera 29/A - 20030 - Seveso (MI) - Italy
-------------------------------------------------------------------------------



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

* Re: [SQL] What do I need to escape in an Insert ?
@ 1998-07-31 18:21  Richard Lynch <lynch@lscorp.com>
  0 siblings, 0 replies; 5+ messages in thread

From: lynch@lscorp.com @ 1998-07-31 18:21 UTC (permalink / raw)
  To: Daniele Orlandi <daniele@orlandi.com>; +Cc: pgsql-sql@hub.org

>I currently use the PHP's addlashed function, is it enought ?

1.  PHP's addslashes function was written for this express purpose, and it
is exactly what you want.  It has been debugged by professionals who know
what they are doing, and has been tested thoroughly by hundreds, if not
thousands, of users.  It is also optimized to be a bizillion times faster
than any regex or PHP code you could possibly come up with.

2.  PHP also has a setting in its php3.ini file which turns on "magic
quotes".  This feature automagically does addslashes to every piece of data
at some point, and maybe removes them at some other point, which is
probably why everything has just been working for you by magic.  I think
the slashes are added during the parsing of the GET/POST/COOKIE parameters.

The novice user frequently doesn't worry about "'/ etc, and PHP covers for
them.  When you really need to output the raw string PHP is throwing
around, you can simply addslashes to the already addslashed string, which
would then display the first set of slashes.

Experts can also turn off addslashes for a miniscule performance gain, and
call addslashes on only the data being sent to a database or webpage.  Of
course, any old scripts need to be checked throroughly for dependencies on
the "magic quotes" function.

If you arbitrarily called addslashes on something that PHP had already
applied addslashes to because of the "magic quotes" feature, it would
probably break things.  Don't do that.  Figure out what "magic quotes"
does, and then what addslashes does, and when/why you would want to use it.

If it's working for you, then just trust the PHP developers to know what
they are doing.  They do.  IE  If it ain't broke, don't fix it.  :-)

Documentation on both of these is available at the extremely well-organized
and multiply-mirrored website:  www.php.net

--
--
-- "TANSTAAFL" Rich lynch@lscorp.com





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


end of thread, other threads:[~1998-07-31 18:21 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-07-30 22:50 What do I need to escape in an Insert ? Daniele Orlandi <daniele@orlandi.com>
1998-07-31 04:42 ` Dan Delaney <dionysos@dionysia.org>
1998-07-31 05:01   ` Bruce Momjian <maillist@candle.pha.pa.us>
1998-07-31 12:26     ` Daniele Orlandi <daniele@orlandi.com>
1998-07-31 18:21 Re: [SQL] What do I need to escape in an Insert ? Richard Lynch <lynch@lscorp.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