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

Posted in ASP, PHP, Programming, Security, SQL by Ivan