[Security] The security craftsman - Part 7

January 8, 2009 - 21:20 UTC   -   Tags: craftsman security testing escaping sql injection "As you saw from your implementation, writing your own security routines isn't always a good idea", Mr. X said looking me straight in the eyes.

I had to admit he was right. If I had already made one easy mistake, there were bound to be others I couldn't easily think of.

"Instead of reinventing the wheel for each project", he continued, "we should rather look at what is made available to us by the frameworks we are using or if there are any external modules that fits our project"
"Ok", I replied, "Anything in mind for the SQL-injection part?"
"Yes. SQL-injection is quite easy to mitigate by allowing the framework to do the output escaping for you. Most frameworks support some sort of parametrized queries."
"But I thought those were invented to support prepared statements", I objected
"You may be right, but they also have an added security benefit. Let's look at some code", he said and moved over to my laptop.

He opened a browser window, and pointed it to http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html. He scrolled a bit down, and showed me the following code:
PreparedStatement updateSales =
con.prepareStatement("UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ? ");
updateSales.setInt(1, 75);
updateSales.setString(2, "Colombian");
updateSales.executeUpdate();
"Now as you can see", he said, "we are replacing the inputs with question marks, and in the two next lines we are telling the framework the data to input and the corresponding data types. This allows the framework to handle the escaping for us"
"So by using prepared statements or parametrized queries we avoid SQL-injection?"
"Well, not really. There are still ways developers can abuse this to reintroduce SQL-injection vulnerabilities. Consider the following"

He copied the code into a text editor, and changed it slightly:
PreparedStatement updateSales =
con.prepareStatement("UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE \""
+ coffeeName + "\"");
updateSales.setInt(1, 75);
updateSales.executeUpdate();
"We are still using the PreparedStatement class, but we are abusing it by concatenating the SQL-statement with input. The application is once again vulnerable."

Continue to part 8...

Go back to: Part 1, Part 2, Part 3, Part 4, Part 5, Part 6