SQL injection attacks

As Steve Friedl's excellent article on SQL injection attacks shows, sloppy programming can lead to disasterous consequences for a database-driven website, especially if that database is Microsoft SQL Server.

Security should be of the utmost importance when building web interfaces but unfortunately, it's not treated as such by a lot of programmers out there. Apart from the specific application input filtering requirements before query execution, here a bunch of general guidelines which might save you some grief:

  1. Only permit localhost connections to the database.

  2. Consider using and additional (md5) encrypted id column when using incremented id's. When running queries, use the encrypted id instead of the incremental id column.

  3. Always, always (md5) encrypt passwords. No exceptions.

  4. Don't design client interfaces which permit direct queries. The only exceptions are partial queries in search engines.

  5. Check all incoming data for nasty submissions and url hacks. All database interaction should be through buttons, links and presented information.

  6. Do not use the "like" keyword. Use "=" instead. Like should only be used for search engines.

  7. Don't design table columns with really obvious names like id, name and address. Same for table names.

PHP already provides a dual anti-hacking mechanism via the mysql_query() and mysql_real_escape_string() functions, but if you're programming in an ASP/SQL Server environment, you'll have to roll your own. The following VBScript function should be useful:

function makesafe(var)
   dim pos
   if not isnumeric(var) then
      var = replace(var, "'", "''")
      var = replace(var, """", "''")
      var = replace(var, ";", VBNULLSTRING)
      pos = instr(1, var, "union select", VBTEXTCOMPARE)
      if pos then
         var = mid(var, 1, pos - 1) & "_" & _
               mid(var, pos, len(var))
      end if
   end if
   makesafe = var
end function

Due to the large volume of spam, comments are disabled. If you have anything to say, please feel free to contact me directly.

About the author

Ivan's mugshotIvan Lutrov is the owner of Lutrov Interactive. He creates cost effective business websites that are simple, engaging and very easy to use. When not busy working on client and personal projects, he's into photography, fishing, cricket, tennis, music from the 70s, cooking, good wine, and apparently knows "way too much" about movies. He tells it like it is, whether you like it or not. Subscribe to the Lutrov Interactive feed via RSS and follow Ivan on Twitter.