Escaping a symbol 1

[fergman]

I've got a system setup that takes input through an input text box and stores it in a database. Unfortunatly I need to have symbols, specifically a '. this of coruse will end a string if it's written out, so I need to escape it. Can someone write a statement to seach a string for all the major symbols, and escape them? for example if '"!`@# or anything like that is discovered then add an escape (/ I think) in front of it.
I don't know how to do this.

 

[yesti]

An anti-hacker type function you could throw into an include file:

Function newstr(str)
Dim tempstr
tempstr=Replace(str, "../", "&quot
tempstr=Replace(tempstr, "--", "&quot
tempstr=Replace(tempstr, ";", "&quot
newstr=Replace(tempstr, "'", "''&quot
End Function

Then you include it:

<!--#INCLUDE FILE = &quot;_include/Fcts.inc&quot; -->

and use it along with some other 'keep your code from crashing' stuff (assuming your table limit is 50 characters:

Dim temp
temp=Left(newstr(trim(Request.Form(&quot;textboxname&quot)),50)

 

[fergman]

ok the replace function will help a lot thanks, but what about dealing with a space, I need to create a table with a space or two, should I replace the space with an _ and then convert it back when pulling up the database? That's a tough one though because I use the table name as a variable, elsewhere to create query's. I'd have to strip the underscore out every time. ugh.

 

[yesti]

No, just enclose the table name with square brackets:

strSQL=&quot;Select * from [table name with spaces]&quot;

Why do you need spaces in the table name? Usually the end user does not need to see the names.

 

[fergman]

It's a long story, but unfortunatly I do, I didnt' realize brackets would work, thanks, will that work for all symbols?

 

[yesti]

I had a similar problem with table names with dashes (-) in them. Enclosing them in brackets solved that problem as well. Other symbols should work, I haven't tried them all, but try to keep symbols out of the table names if possible

 

[Genimuse]

Brackets work around table and field names (and views and stored procedures and such), but are not for literal strings, which must be enclosed in single-quotes. As noted above, two single-quotes in a row is interpreted as a single-quote character rather than a string delimiter.

 

[onpnt]

umm.. why not use Server.HTMLEncode(string)

____________________________________________________
_{The most important part of your thread is the subject line.
Make it clear and about the topic so we can find it later for reference. Please!! faq333-2924}

http://www.onpntwebdesigns.com

 

[fergman]

Server.HTMLencode would just strip off html tags wouldn't it, would that actually work?

 

[onpnt]

no!!

http://www.devguru.com/Technologies/asp/quickref/server_htmlencode.html

____________________________________________________
_{The most important part of your thread is the subject line.
Make it clear and about the topic so we can find it later for reference. Please!! faq333-2924}

http://www.onpntwebdesigns.com

 

[fergman]

Ok, I am a little confused on how to do this, here is my create table query...


sqlQuery = &quot;CREATE TABLE &quot; & newCompany & &quot;(openPOs VARCHAR(50) NULL, adminPage VARCHAR(50) NULL, adminQuantity VARCHAR(5) NULL, adminItemNumber VARCHAR(50) NULL, adminDescription VARCHAR(50) NULL, adminEachPrice VARCHAR(50) NULL, adminItemNotes VARCHAR(50) NULL, adminAdded VARCHAR(50) NULL)&quot;

do I enclose the whole thing in brackets?

 

[onpnt]

sqlQuery = &quot;CREATE TABLE &quot; & Server.HTMLEncode(newCompany) & &quot;(openPOs


as Genimuse stats already that you should just suround the var's with [ ] in teh SQL.

after having a chance to read through the thread this wouldn't work completely for the char's in question. escape() the values then insert them. a replace function shouldn't be needed if you do that.
as is
var str = escape(str); //javascript on the client level

____________________________________________________
_{The most important part of your thread is the subject line.
Make it clear and about the topic so we can find it later for reference. Please!! faq333-2924}

http://www.onpntwebdesigns.com

 

[onpnt]

errmmm.... I apologize I really meant URLEncode which will perform the same task as the escape()

to many functions.....

____________________________________________________
_{The most important part of your thread is the subject line.
Make it clear and about the topic so we can find it later for reference. Please!! faq333-2924}

http://www.onpntwebdesigns.com

 

[onpnt]

here's a example (again apologize for the the misdirection. been working to hard I think)
<html>
<body>
<%
Dim str, sql
str = &quot;blah. blah - and more /(&_$%*(&*blah!&quot;
sql = &quot;INSERT INTO table &quot; & Server.URLEncode(str)

Response.Write &quot;Before Server.URLEncode str variable holds&quot; & str & &quot;<br>&quot;
Response.Write &quot;After Server.URLEncode &quot; & Server.URLEncode(str) & &quot;<br>&quot;
Response.Write &quot;The sql statement comes out as &quot; & Server.URLEncode(sql) & &quot;<br>&quot;
str = Server.URLEncode(str)
%>
To undue the escape just javascript unescape() it
<script language=&quot;javascript&quot;> document.write(unescape(&quot;<%=sql%>&quot.replace(/\+/g,&quot; &quot); </script>
</body>
</html>


live

http://onpntwebdesigns.com/serverhtmlencode.asp

____________________________________________________
_{The most important part of your thread is the subject line.
Make it clear and about the topic so we can find it later for reference. Please!! faq333-2924}

http://www.onpntwebdesigns.com

 

[fergman]

Well I've come up with a working query, (mostly) it doesn't allow things that terminate a string (' &quot; etc...) but that's fine, I can make constraints that don't allow those. I don't think they are valid in a table name anyway. thanks for the help.

sqlQuery = &quot;CREATE TABLE [&quot; & newCompany & &quot;](openPOs VARCHAR(50) NULL, adminPage VARCHAR(50) NULL, adminQuantity VARCHAR(5) NULL, adminItemNumber VARCHAR(50) NULL, adminDescription VARCHAR(50) NULL, adminEachPrice VARCHAR(50) NULL, adminItemNotes VARCHAR(50) NULL, adminAdded VARCHAR(50) NULL)&quot;