website security

Is my web developer correct?

[Simon606]

I recently upgraded my database from access to SQL. My web developer says I don’t need stored procs because the cleanuptext function I already have is good enough to protect the website. Is this true?

[Steerpike]

Nobody can answer that question without knowing what your cleanuptext function does.
The best kind of general answer I can give is that stored procedures are not a necessity for security, that you should never trust variables without testing and cleaning them thoroughly first, and that moving from SQL to access is probably a move in the right direction.

[LifeinAsia]

While that statement may or not be valid, you may still want/need to use stored procedures for increased speed.

Since we don't know exactly what your devloper said, or what his intent was, we don't know if he means:
A) "You don't need to use stored procedures to prevent against SQL injection attacks, because the cleanuptext fuunction provides the same level of security," or
B) "You don't need to use stored procedures at all."
I would be suspicious if he is tryig to say B- it sounds ike he is really saying "I don't know how to code stored procedures."

And I think Steerpike meant "from Access to SQL," not the other way around. :)

[Simon606]

haha I hope Steerpike meant that too :)

My developer thinks his cleanuptext is good enough on its own to secure the website against SQL attacks.

The problem I have is my site is a community site so I have to allow words like Drop, Select, Union etc. So the only thing his cleanup code does is replace the symbol keys.

[vincevincevince]

What language is he writing in? Words such as drop, select, union etc. are fine; but the function needs to ensure they can only get into 'safe' parts of the SQL.
You need to judge your developer by his level of expertise and his experience; if in doubt as another developer to review the cleanuptext function.

[Simon606]

This is my cleanup script. It is written in ASP

function cleanuptext(strval)
on error resume next
'strval = Replace(strval, "exec", "")
'strval = Replace(strval, "select", "")
'strval = Replace(strval, "drop", "")
'strval = Replace(strval, "insert", "")
'strval = Replace(strval, "delete", "")
'strval = Replace(strval, "join", "")
'strval = Replace(strval, "script", "")

'strval = Replace(strval, "EXEC", "")
'strval = Replace(strval, "SELECT", "")
'strval = Replace(strval, "DROP", "")
'strval = Replace(strval, "INSERT", "")
'strval = Replace(strval, "DELETE", "")
'strval = Replace(strval, "JOIN", "")
'strval = Replace(strval, "SCRIPT", "")

'strval = Replace(strval, "Exec", "")
'strval = Replace(strval, "Select", "")
'strval = Replace(strval, "Drop", "")
'strval = Replace(strval, "Insert", "")
'strval = Replace(strval, "Delete", "")
'strval = Replace(strval, "Join", "")
'strval = Replace(strval, "Script", "")

strval = Replace(strval, "<", "(")
strval = Replace(strval, ">", ")")
strval = Replace(strval, "=", "equals")
strval = Replace(strval, "'", "")
strval = Replace(strval, "XP_", "")
strval = Replace(strval, "--", "")
strval = Replace(strval, "[", "(")
strval = Replace(strval, "]", ")")
on error goto 0
cleanuptext = strval
end function

How do I ensure words like Select and Drop only appear in the safe parts of SQL?

[vincevincevince]

That script won't be good enough I'm afraid. Your programmer does not seem to know what he's doing. I suggest you post in the databases forum where you'll get some really good responses along the lines of "How can I sanitise SQL statements in ASP?"

In any case; such a simplistic approach is full of holes; just take the case of:
DRDROPOP

Your cleanuptext function will replace it with:
DROP

Likewise: XPXP__ etc.

Great ;)

If you can't persuade your programmer to study a little more SQL, then you need to either:

Insist (as a client you always have this right) on him using stored procedures

Hire someone with more SQL experience to come in as a 'database engineer' to patch up the SQL work on the development and hopefully give your programmer a few tips

[GaryK]

My suggestion is a bit radical. If you can't trust your programmer to understand safer coding procedures for SQL Server, how can you trust him/her to understand safer coding procedures for anything? There's a lot more at risk than SQL injection attacks. Even something as seemingly simple as form input needs to be properly sanitized before you can risk doing anything with it. Quick example. Is your programmer making sure things like JavaScript commands and other potentially harmful HTML elements are being filtered out before being allowed to be posted in a message? You need to find a competent programmer. Sorry to be so blunt but programmers are a dime a dozen. Good programmers are more expensive, but well worth it.

[Simon606]

Interesting comments. Certainly plenty of food for thought. Thank you for all your help.