The apostrophe (') in SQL statements

Anon

Hi,

I am having a problem with SQL statements written within ASP code.

Whenever the user enters a text in a form that include the apostrophe symbol ('), It causes a serious problem (this will close the SQL statement and will eliminate the rest of the text)

Is there a way to deal with such a problem, like replacing any (') with ('') so that it'll force it to write ONE (') ?

Anon

Replace(Request.form("formfield"), "'", "")

And of course, the formfield text would be replaced by the name of YOUR formfield

Anon

Well, thanx Tek, but I think this would just clear this apostrophe, right ?

so If someone types (don't), it'll appear as (dont) and I wan it to be as is.

Anon

How about this:
Replace(Request.form("formfield"), "'", "\'")
?

Anon

It didn't work

Anon

Taleb,

I use this frequently. The function Checkstring looks for the ' and replaces it with '', thus allowing users to type as they normally do and keep the data intact. The 2nd part of this code is where I call the function Checkstring when I write a form field's contents to a database.

<%
Function CheckText(checkstring)

If instr(checkstring,"'") > 0 then

checkstring = replace(checkstring,"'","''")     
end if  
CheckText = checkstring

End Function %>

.... and then call the function like this...

Sql = Sql & ", '" & CheckText(Request.Form("outagetime")) & "'"

Hope this helps.

Anon

this works as I have tried it.

Replace(Request.form("formfield"), "'", "''")

Anon

I'm looking for the syntax for doing the same in PHP. I have done it in ASP as shown in the other replies but I'm not able to duplicate it in PHP.

Anon

In SQL you have to find and replace a single quote (apostrophe) with two single quotes.
Exaple : update people
set last_name = 'D''Angelo'
where Id = 23

will store D'Angelo in the database.

Anon

I am following up with 2 basic questions:

(1) is there really no easy way to change a MySQL data field so that it will accept apostrophes (')?

(2) if not, has anyone found a simple fix that works in php?