My SQL Injection Question

At this point in my career, I have now spent more time interviewing job candidates than I have interviewing for jobs. With that I’ve put a lot of thought into what questions should I ask, and have thrown quite a few of them out. The following is an explanation of one of the security questions that I often ask candidates.

I usually like to ask candidates at least 1 security question. I’m typically trying to gauge that they will know the basics. They don’t need to be security experts, but I want to see that they have enough knowledge and experience around some common security problems. When given the right environment and tools, they will fall into the pit of success. I also want to see how the candidates react when thrown a curve ball. So I usually start out with a straight forward question that shows that the candidate has the required knowledge and then I attempt see how they handle something they likely don’t know.

One of the topics that I usually broach is SQL injection. Candidates almost always mention proper parameterization. They typically see it as an issue where someone has improperly concatenated strings to form a SQL command. I’ve had some candidates recommend using Stored Procedures instead of Creating SQL commands from strings. These details as part of their responses relating SQL injection all usually give me enough information that the candidate has the baseline security knowledge that we need.

After that I gather that, I follow up by flipping the script. I ask them some variation of:

Given modern architecture the attacker is unlikely to bind data they want to the view models, and have it show on the page or API output; how do attackers exfiltrate the data from a system vulnerable to a SQL injection?

I don’t care if they can answer this question. I want to see how they react, and if they’re willing to go along with the question. And frankly it’s interesting to see what people come up with in the face of this question.

I have had candidates get destructive. Missing the point that they’re supposed to get the data back out somehow. The see it from the perspective that an attacker frustrated on not being able to directly see the data would instead turn to destroying it by dropping tables and deleting records. I usually don’t like to see people go this route, but it’s pretty easy to remind them that the point of the exercise is to get the data out and not destroy it. So I usually see this as an acceptable tangent and hope that I can get the candidate back on track.

One candidate tried to figure out how to manipulate the database data to fit the API output. In terms of assessing this candidate, I thought that that was rather clever and it’s definitely in the mold of the interesting answers that I want to see from people. I appreciate the creativity, and their willingness to go along with me and discuss this.

Another candidate, who was dead set that stored procedures should be used to access the database, argued with me that if the system was using stored procedures this wouldn’t be possible. I did point out that you can write stored procedures that are vulnerable to SQL injection, which I know from the experience of accidentally making one (I’m thankful that mistake was caught before it made it to production). Usually when I see a candidate dig in like this on a particular detail during an interview it’s usually a red flag that they might not be willing to listen or are a bit inflexible.

With some back and forth discussion, I’ve had this question meander into other appropriate technical questions for the position. One candidate when prompted with a hint that timing might be important brought up async/await. That led to me taking a tangent to ask some questions about how coroutines are written in dotnet, and see what they knew about asynchronous execution.

And finally with some other hints and hinting follow up questions, I’ve used this prompt to see what candidates know about various debugger tools. After all, what sort of information can someone glean from the Chrome Dev Tools or Fiddler? I really enjoy when this comes up, because it can show me a little bit more of the creative problem solving that a candidate will bring to bare on the problems our team faces.

Once the conversation has ran it’s course, I usually let the candidate know the answer. Some candidates outright ask for the answer, which I usually count as a positive. Others I giver the answer in the form of one last question: what do you know of side channel attacks? This question itself was inspired by a book that I read. The Web Application Hacker’s Handbook has some interesting tutorials on the basics of SQL injections, and has a breakdown of how SQL injection can be used as a side channel attack. The quick and dirty explanation is that if the data isn’t being returned in the response directly, the timing of the HTTP response can be manipulated. This is accomplished by introducing a SQL directive that adds wait time to the execution of the SQL command. That delay can then be predicated on the presence of information within the database, so if the first character of the plain text password for the admin is 'p', then an extra 300ms will be added to the HTTP round trip time.

I want to re-iterate that I don’t expect candidates for software engineering roles at my company to know this off the cuff. We’re not hiring security experts, but I have found this to be an excellent bridge from standard baseline security questions into something that shows me more of how a candidate thinks and reacts to something hard or unknown.