replace characters when INSERT

[weee]

Hi.

I've been told tht I need to replace certain characters when I'm inserting info into a database.

I would like to know how to do it (a simple exsample will be great) and what do I need to replace but those:
;
&
<
>

Thanks!

[CrazyC115]

When inserting text into a database the ONLY character you need to replace is the single quote ' with 2 single quotes ''.

When retrieving the data back from the database you can use Server.HTMLEncode(my_string). That function will replace all the < with &lt;, > with &gt;, & with &amp; etc...

If you really want to replace all sorts of characters though the easiest way is with a regular expression.

How to use Regular Expressions. (http://www.devguru.com/Technologies/vbscript/quickref/regexp.html)


FUNCTION cleanupString(my_string)
DIM oRegExp

oRegExp = NEW RegExp
oRegExp.Global = TRUE 'True to replace ALL instances of characters
oRegExp.IgnoreCase = FALSE 'Set to true if you dont care is a replaces A etc.
oRegExp.Pattern = "[\>\<\&\;\']"

cleanupString = oRegExp.Replace(my_string, "")
END FUNCTION

[weee]

I can't see what those character beenig reaplce with?

[CrazyC115]

I had the characters replaced with "" (or nothing). If you want to replace the characters with their proper &; code you might want to do something like this.


FUNCTION cleanupString(my_string)
DIM oRegExp, sCleaned

oRegExp = NEW RegExp
oRegExp.Global = TRUE 'True to replace ALL instances of characters
oRegExp.IgnoreCase = FALSE 'Set to true if you dont care is a replaces A etc.

oRegExp.Pattern = "[\>]"
sCleaned = oRegExp.Replace(my_string, "&gt;") 'Start with my_string

oRegExp.Pattern = "[\<]"
sCleaned = oRegExp.Replace(sCleaned, "&lt;") 'Switch to sCleaned

oRegExp.Pattern = "[\']"
sCleaned = oRegExp.Replace(sCleaned, "''")

oRegExp.Pattern = "[\&]"
sCleaned = oRegExp.Replace(sCleaned, "&amp;")

cleanupString = sCleaned
END FUNCTION

[weee]

I think I'm starting to understand...

How the SQL will look like?

[CrazyC115]

Originally posted by weee
I think I'm starting to understand...

How the SQL will look like?

lets say this some data a visitor to your website entered into a textarea.

Hello my name is <b>Craig</b> and i'm an ASP programmer

After running the first function I supplied the string will look like:

Hello my name is bCraigb and im an ASP programmer
I did not have it strip the html tags, just the < and > characters

After running the second function I supplied the string will look like:

Hello my name is &lt;b&gt;Craig&lt;/b&gt; and i''m an ASP programmer

Now when you put that string into a SQL Query/Insert/Update/Delete your SQL might look like:


SQL = "INSERT INTO some_table (my_string_column) VALUES ('" & my_clean_string & "')"
Response.Write(SQL)


The output of the above code would look like the following if i used the second function to clean the string submitted by a user.

INSERT INTO some_table (my_string_column) VALUES ('Hello my name is &lt;b&gt;Craig&lt;/b&gt; and i''m an ASP programmer')

I hope that helps, personally the only thing I ever cleanup in my strings are the single quotes -> ' <- and replace them with 2 single quotes -> '' <-. That way if I choose to allow users to submit HTML code I just pull the string out of the database and display it on my webpage. Yes I know not stripping HTML code out of the submitted data is bad, as people can break your webpage by not using HTML tags properly.

[weee]

FUNCTION cleanupString(my_string)
DIM oRegExp, sCleaned

oRegExp = NEW RegExp
oRegExp.Global = TRUE 'True to replace ALL instances of characters
oRegExp.IgnoreCase = FALSE 'Set to true if you dont care is a replaces A etc.

oRegExp.Pattern = "[\>]"
sCleaned = oRegExp.Replace(my_string, "&gt;") 'Start with my_string

oRegExp.Pattern = "[\<]"
sCleaned = oRegExp.Replace(sCleaned, "&lt;") 'Switch to sCleaned

oRegExp.Pattern = "[']"
sCleaned = oRegExp.Replace(sCleaned, "''")

oRegExp.Pattern = "[\&]"
sCleaned = oRegExp.Replace(sCleaned, "&")

cleanupString = sCleaned
END FUNCTION


SQL = "INSERT INTO some_table (lname,fname) VALUES ('" & cleanupString(lname) & "', '" & cleanupString(fname) & "', )"

That's how the syntax will be?

[CrazyC115]

Originally posted by weee
FUNCTION cleanupString(my_string)
DIM oRegExp, sCleaned

oRegExp = NEW RegExp
oRegExp.Global = TRUE 'True to replace ALL instances of characters
oRegExp.IgnoreCase = FALSE 'Set to true if you dont care is a replaces A etc.

oRegExp.Pattern = "[\>]"
sCleaned = oRegExp.Replace(my_string, "&gt;") 'Start with my_string

oRegExp.Pattern = "[\<]"
sCleaned = oRegExp.Replace(sCleaned, "&lt;") 'Switch to sCleaned

oRegExp.Pattern = "[']"
sCleaned = oRegExp.Replace(sCleaned, "''")

oRegExp.Pattern = "[\&]"
sCleaned = oRegExp.Replace(sCleaned, "&")

cleanupString = sCleaned
END FUNCTION


SQL = "INSERT INTO some_table (lname,fname) VALUES ('" & cleanupString(lname) & "', '" & cleanupString(fname) & "', )"

That's how the syntax will be?

Oh yes, silly me I think i need to go back to school and learn how to read ;-)


SQL = "INSERT INTO some_table (lname,fname) VALUES ('" & cleanupString(lname) & "', '" & cleanupString(fname) & "')"
code fixed

You had an extra , after the last field which will cause a SQL error. But other wise yup thats how it will look in your code.

[CrazyC115]

Just notice this page is converting my -> & a m p ; <- to &... in the function make sure you replace the & with -> & a m p ; <- without the spaces and not &, as you will just be replacing the & symbol with another & and not the "HTML safe" version.

[weee]

Thanks a lot man.

Let me ask you plese one more general question.
Why do other people don't use regular expression but just simple functions to replace?
what's the difference?

[CrazyC115]

the Replace funtion uses RegExp also... you just don't see it.

I like to use RegExp because you can do alot more with it then you can with the plain ol' replace function. But mainly it's just programmer preference, if you want to use Replace() go ahead or continue to use the RegExp :).

It's the same difference as people who use Frontpage vs Notepad to code HTML. Both program's get the job done, one is just harder to use. I'll let you figure out which program ;-)

[weee]

Thank you for your time and supprot.

Take Care man!