Welcome to WebmasterWorld Guest from 54.160.254.203

Forum Moderators: phranque

Message Too Old, No Replies

website security

Is my web developer correct?

     
10:05 am on Sep 11, 2008 (gmt 0)

Junior Member

5+ Year Member

joined:Nov 9, 2006
posts:49
votes: 0


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?
3:15 pm on Sept 11, 2008 (gmt 0)

Junior Member

10+ Year Member

joined:Oct 27, 2005
posts:112
votes: 0


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.
3:31 pm on Sept 11, 2008 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5551
votes: 24


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

4:23 pm on Sept 11, 2008 (gmt 0)

Junior Member

5+ Year Member

joined:Nov 9, 2006
posts:49
votes: 0


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.

6:42 pm on Sept 11, 2008 (gmt 0)

Senior Member from MY 

WebmasterWorld Senior Member vincevincevince is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Apr 1, 2003
posts:4847
votes: 0


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.
10:32 pm on Sept 11, 2008 (gmt 0)

Junior Member

5+ Year Member

joined:Nov 9, 2006
posts:49
votes: 0


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?

1:04 am on Sept 12, 2008 (gmt 0)

Senior Member from MY 

WebmasterWorld Senior Member vincevincevince is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Apr 1, 2003
posts:4847
votes: 0


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
  • 4:49 am on Sept 12, 2008 (gmt 0)

    Senior Member

    WebmasterWorld Senior Member 10+ Year Member

    joined:Sept 17, 2002
    posts: 2251
    votes: 0


    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.
    6:09 pm on Sept 15, 2008 (gmt 0)

    Junior Member

    5+ Year Member

    joined:Nov 9, 2006
    posts:49
    votes: 0


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