Обсуждение: What do I need to escape in an Insert ?
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 -------------------------------------------------------------------------------
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.
> 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)
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
-------------------------------------------------------------------------------