1

I am investigating a SQL injection attack issue (SQL Server database in this case). Before anyone suggests it, I know that parameterised queries are the ultimate solution but that is not going to be easy or straight forward in this legacy code, so bear with me.

The puzzle is that this (malformed) command 'batch' actually works:

select * from table1 where tableCode LIKE '' delete from table1

Now my understanding of the SQL Server documentation is that this is not a legitimate command batch because there is no semi-colon separator, so strictly speaking it should be rejected by SQL Server. However it isn't and the two commands are executed correctly, with the expected result.

Is this a case of SQL Server not conforming to its own documentation, or have I missed something?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
haughtonomous
  • 4,602
  • 11
  • 34
  • 52
  • 1
    Can you provide the link where Microsoft says that semicolon is required? here is MSDN https://msdn.microsoft.com/en-us/library/ms177563(v=sql.110).aspx `Transact-SQL statement terminator.Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version.` It is not mandatory in Sql Server 2014. Who knows when it will be mandatory?! Even on 2016 Microsoft says that not using semicolons are deprecated. – Giorgi Nakeuri Dec 14 '15 at 11:20
  • Useful info: http://stackoverflow.com/a/710697/1115360 – Andrew Morton Dec 14 '15 at 11:21
  • https://msdn.microsoft.com/en-us/library/ms712553%28v=vs.85%29.aspx This doesn't says that the semi-colon is optional. In fact it defines a batch as a number of commands separated by a semi-colon. But thanks for pointing me to the alternative documentation - I hadn't seen that. – haughtonomous Dec 14 '15 at 11:25
  • I'm just wondering what the case for MS not conforming to ANSI SQL-92 from the outset mght have been? I can see why enforcing it now might lead to all sorts of difficulties with extisting databases implemented by their customers over the years, but I am curious why they got into this position in the first place. – haughtonomous Dec 14 '15 at 11:50
  • 1
    @NeilHaughton: your link is for the **ODBC** connection method - which is usually not used because it's old and poorly supported. T-SQL as a language does **NOT** require semicolons to separate commands - it's encouraged, and a few commands start to require it - but for the most part, it's **not** mandatory in straight T-SQL – marc_s Dec 14 '15 at 11:58
  • 1
    "what the case for MS not conforming to ANSI SQL-92 from the outset mght have been" given that the genesis for the product was Sybase server which had (at least) two releases pre-dating 1992, can you not guess? – Damien_The_Unbeliever Dec 14 '15 at 12:00
  • Sigh..., yes I can guess. :-) – haughtonomous Dec 15 '15 at 10:20

1 Answers1

2

This is a perfectly valid SQL Statement for SQL Server. The semicolon in all current editions is optional. MSDN documentation states that the best practice is to use semicolons to end SQL statements because the optional will soon be deprecated.The semicolon is a part of the ANSI SQL-92 standard, but was never used within Transact-SQL.

S.Karras
  • 1,483
  • 15
  • 19