SQL Injection seems to be a pretty common theme around here on TDWTF. It's not so much pointing out SQL Injection vulnerabilities (aside from that one state that leaked a whole bunch of SSNs), but instead exploring all those "unique" ways that developers try to address the problem. Well, here are three more to add to the mix.

"I found this particular snippet in the external sales portal at my company," wrote Ben, "it's a technique that appears throughout the site in various queries. This was their attempt to protect the database from SQL injection attacks."

$sql = "select * from customers where "
       . "email_address = '" . base64_decode(base64_encode($email_address)) 
       . "' and password = '" . base64_decode(base64_encode($password)) . "'";

As it turned out, encoding a string in Base64 and then decoding it doesn't do all that much. It did feel pretty sneaky, though.

 

Vincent Ballard found this next snippet in the authentication code for the project he'd been assigned to.

// The following string is an SQL comment, and could 
// blank out the check for password in our SQL statement 
// if used in the username!
if (username.indexOf("';--")!=-1) {
    throw new AuthorisationException(username
      +" given as login name contains ';--, this is bad for SQL!"); 
}

// Get the (hopefully single) id of the player with 
// this name and password 
ResultSet authorised = statement.executeQuery(
    "SELECT id FROM table_name_redacted WHERE "
    + "username='"+username
    +"' AND password='"+password+"';");

It's a bit better, but doesn't quite fool the hacker who uses the "'; --" attack instead of "';--".

 

Surprisingly, today's most effective injection prevention comes from a whole bunch of financial institutions, thanks software provided by Harland Financial Solutions. Instead of bothering with that "parameterized query" nonsense, they just simply ban all hacker words:

Why can’t I use certain words like "drop" as part of my Security Question answers?
There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

You have to especially be careful about that last one on the list. When you start throwing the n-word around, systems will be hacked.