Error while insert variable with apostrophe into database...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

seowhua - 31 Oct 2005 15:41 GMT

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 :)

Signature

regards seowhua

Reply to this Message

Evertjan. - 31 Oct 2005 15:55 GMT

=?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!

Signature

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

Reply to this Message

Bob Barrows [MVP] - 31 Oct 2005 18:09 GMT

> 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
[quoted text clipped - 6 lines]
> 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&selm=e6lLVvOcDHA.1204%4
0TK2MSFTNGP12.phx.gbl

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

http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d32
2b882a604bd

/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.inetserver.asp.db/msg/72e36
562fee7804e

Bob Barrows

Signature

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"

Reply to this Message

Webmaster Forum / ASP / Database Access / October 2005

Error while insert variable with apostrophe into database...