Insert into database....best practice

[dallasweb]

When you insert user supplied information from a form(text, textarea) and insert into a database, what is the best method for keeping HTML, hacks and other crap from being inserted?

I'm currently using URLEncodedFormat to convert the info before insert and URLDecode during output.

I really don't want to do a Replace(crap, "XXXX", "all&quot.

What is best practice for this?

Thanks!

 

[Silvano]

why don't you simply insert data into database without URLEncodedFormat? (if you need URLEncodedFormat, you can always use it later during output, right?) Sylvano
dsylvano@hotmail.com

"every and each day when I learn something new is a small victory..."

 

[dallasweb]

If, for example a person is submitting a name "O'Brien" the single quote will kill my insert statement. There are other special characters that will kill the insert statement.

I'm looking for a standard method that CF programmers are using to catch these plus any hack attempts, etc...

I've looked at a few custom tags on dev exchange and most loop over the URL and FORM var's and replace problem characters and "drop table" statements.

Any best practice help is appreciated.

Thanks

 

[Silvano]

what database are u using? on

http://www.worldwebspiders.com

I have access and I am not escaping those characters but still have no problems whatsoever. you can test submission form there (use #### and '''' for user name and/or pass), you will not get any errors if u use # or ' Sylvano
dsylvano@hotmail.com

"every and each day when I learn something new is a small victory..."

 

[Glowball]

We have a small script that changes all < signs to

& lt ;

(take those spaces out) and all > signs to

& gt ;

We have lots of warnings that HTML will be stripped, so it shows the tags instead of the actual HTML. If you're having problems with single quotes you can always use

PreserveSingleQuotes()

to keep them safe.

Also, if you are doing an INSERT and someone puts in SQL it shouldn't do any damage. In other words, your statement will end up something like

INSERT INTO mytable 
    (someval, anotherval)
VALUES 
    (12, 'DROP TABLE mytable')

which is fine (though might not make any sense). Hope that helps!