Re: [SQL] What do I need to escape in an Insert ?

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(at)Dionysia(dot)org www.LouisvilleTimes.org
www.Dionysia.org/~dionysos/ Dionysia Design
ICQ Number: 8171285 www.Dionysia.com/design/
-----------------------------------------------------------------------
I doubt, therefore I might be.