Every now and again I explain to people what “SQL Injection” is. I generally do this by writing a bit of an SQL string for them, using a string which can be manipulated (one which is vulnerable to this particular exploit, down at the database level of the application). And I then show them this XKCD comic:
So, for example, I’d define the following as an example of a vulnerable stored procedure:
create procedure SaveStudent @StudentName nvarchar(256) as declare @sql nvarchar(max) set @sql = 'insert into Students ( StudentName ) select ' set @sql = @sql + '''' + @StudentName + '''' exec(@sql) go
I would then put in the example name from the XKCD comic above to demonstrate just what ends up happening. Let’s say you were to call that stored procedure, passing in Little Bobby Tables’ name as the variable:
exec SaveStudent 'Robert''); DROP TABLE Students;--'
The stored procedure would then execute the following commands:
insert into Students ( StudentName ) select 'Robert'); DROP TABLE Students;--'
So … why is this a problem? One salient point is that the apostrophe character is used to enclose strings in quotation marks. The way Bobby’s name is constructed allows for a malicious command to be sent to the database (the Students table is erased by the command DROP TABLE Students;), and no error being necessarily returned, because Bobby’s name fits in perfectly with how SQL works – he’s got a proper semicolon, terminating the command that comes before, so he was added to the table … but then the table was dropped, using a valid command, and everything after that is commented out (the double-dash is a comment marker), so there wouldn’t necessarily be any errors at all coming out of this – it’s perfectly valid, it’s running in a privileged context (it has been “blessed” by being turned into a stored procedure, so it’s trusted to run).
All of this is the lead up to the punchline, which is this company, apparently registered in the UK (or, something used for testing, I suspect, as this is the beta for UK Government’s Companies House): ; DROP TABLE “COMPANIES”;– LTD. This, passed into the above sample procedure, will yield the following SQL string:
insert into Students ( StudentName ) select '; DROP TABLE "COMPANIES";-- LTD'
Now, this one’s going to throw an error, because it’s actually improper syntax no matter which database you pass it to (well – any of those I have used, anyway, which is … way too many). However, if this is indeed used for debugging or as a demonstration, it will 1) throw an error if you feed it to anything that’s vulnerable to this exploit, 2) hopefully not throw an error anywhere, because the UI is supposed to be sanitizing these inputs, so it should be properly formatted (“escaped”) so as not to cause this problem. I can see this value being used both to test the UI (put it in & see if some database code which is intentionally vulnerable to this exploit throws an error) and also to test the database code, for scenarios which do not use a user interface such as loading in data from another application or a programmatic interface.
This technique is not just dangerous because it allows things to be broken; this technique is routinely used to exfiltrate data such as usernames and passwords, credit cards, or whatever other juicy details are in the database. If an adversary can figure out just which poor programming technique was used, and can figure out how errors are presented to the webpage, then they can intentionally cause errors which return data which should remain secret, or they can simply replace the query that’s supposed to do something legitimate (pull back a list of toasters, for example) with a query that returns that sensitive data right onto the webpage.
In any event, once you understand this humor as a programmer, your programming fundamentally changes, as there are only a handful of bad programming techniques which allow for this kind of vulnerability – so, you quickly eradicate those techniques from your practice (and hopefully go back and clean things up in older code). The fact that a huge number of websites are vulnerable to this tells you something (bad) about the competency of people who write and test code. I will not rant here about the many programmers who think about databases as being simply dumping grounds for data, rather than fully-functional programming environments.
-D