Error while insert variable with apostrophe into database...

Hi All,

I face with problems while inserting variable with apostrophe sign (') into
the database... I receive error message everytime while the variable to add
into databases include a ' sign. The sample coding are as below:

set Con=createobject("adodb.connection")
Con.open "dsn=Indicator"

set Cmd=CreateObject("ADODB.Command")
Cmd.ActiveConnection=Con

str="INSERT INTO student"
str=str &"(stud_id, stud_name, stud_tel, stud_add)"
str=str &" VALUES('"&stud_id&"', '"&stud_name&"', '"&stud_tel&"',
'"&stud_add&"')"

Cmd.CommandText = str
Cmd.Execute



Appreciate for every help. Thanks :)

--
regards seowhua

Re: Error while insert variable with apostrophe into database...

=?Utf-8?B?c2Vvd2h1YQ==?= wrote on 31 okt 2005 in
microsoft.public.inetserver.asp.db:

> I face with problems while inserting variable with apostrophe sign (')
> into the database... I receive error message everytime while the
> variable to add into databases include a ' sign. The sample coding are
> as below:
>

NEVER send clientside strings directly to the SQL string, this gives a
security risk.

function noApostr(x)
noApostr = replace(x,"'","`")
end function

But this is not enough!

--
Evertjan.
The Netherlands.
(Replace all crosses with dots in my emailaddress)

Re: Error while insert variable with apostrophe into database...

seowhua wrote:
> Hi All,
>
> I face with problems while inserting variable with apostrophe sign
> (') into the database...

What type and version of database?

> I receive error message everytime while the
> variable to add into databases include a ' sign. The sample coding
> are as below:
>
> set Con=createobject("adodb.connection")
> Con.open "dsn=Indicator"
>
Nothing to do with your problem, but avoid odbc:
http://www.aspfaq.com/show.asp?id=2126

> set Cmd=CreateObject("ADODB.Command")
> Cmd.ActiveConnection=Con
>
> str="INSERT INTO student"
> str=str &"(stud_id, stud_name, stud_tel, stud_add)"
> str=str &" VALUES('"&stud_id&"', '"&stud_name&"', '"&stud_tel&"',
> '"&stud_add&"')"
>
> Cmd.CommandText = str
> Cmd.Execute
>
>
>
Avoid dynamic sql:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

Instead, use parameters either via saved parameter queries (Access):
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/b3d322b882a604bd

/stored procedures (SQL server):
http://tinyurl.com/jyy0

or by using a Command object to pass arguments to a string containing
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"