February 3, 2010 at 4:32 am
Very nice question. Nice SQL inject with a side effect.
I hope I will never use something like this one.
February 3, 2010 at 5:39 am
An excellent question. The best questions get you thinking in new ways about tools you use all the time, and this certainly fits the bill.
Ultimately it came down to a coin-flip for me (which I consider a fail regardless of the fact that I picked the right answer), but once I read the explanation it made perfect sense. Of course the DSQL is going to execute completely before the insert takes place. Duh!
That said, I would never write code like this, but who knows... some day I might need a magic trick, and this example might point me in the right direction! 🙂
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
February 3, 2010 at 6:11 am
nice question sir
February 3, 2010 at 6:45 am
I guess the color coding in QA/SSMS does it? Paste it instead into Notepad++ or an equal text editor that has color coding for SQL, so you can't accidentally execute the code...
Anyone who has to deal with SQL injection can learn from this though, so I think it's a good question.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
February 3, 2010 at 7:38 am
Thanks for a good question. For some reason, I was thinking that what would be inserted would be the deletion of the table.
February 3, 2010 at 8:06 am
Very good question. I should had executed it on SSMS and then answered it. But, no I just read and answered and got it wrong.
SQL DBA.
February 3, 2010 at 8:32 am
SanjayAttray (2/3/2010)
Very good question. I should had executed it on SSMS and then answered it. But, no I just read and answered and got it wrong.
What would have running it in SSMS and putting the answer in have achieved apart from getting you the points?
February 3, 2010 at 10:21 am
This is an excellent question, I thoroughly enjoyed it. I guess the reason why initially it is tempting to select 0 as a correct answer is incorrect assumption about when does the DSql gets injected. In other words, if the insert into portion was a part of the DSql itself then the answer would be a no-brainer (0). To figure that the execution works as it is explained is not easy, but of course makes perfect sense. There is an insert into followed by some statement to parse, and the engine expects the latter to possibly have something selected from somewhere. The select portion of the DSql does it, the delete does not change the originally selected data as it is already on the heap, and so the insert of 4 rows takes place.
I wish we had more questions like this! Thank you!
Oleg
February 3, 2010 at 10:42 am
Thank you for all your comments.
Unfortunately no explanation links to MSDN or BOL ... probably because of that's a simplified real life example 🙂
Actually to believe in this kind of "functionality" I had to develop my own "clean" code that was published later as QoD.
Thanks,
Gennadiy
February 3, 2010 at 12:15 pm
I hadn't considered the SQL Injection aspect of this question. Thanks for pointing that out.
February 3, 2010 at 12:24 pm
It is interesting how the engine behaves when the second part of DSql changes to something different. For example, if instead of deleting the records from the table the second part of the DSql tries to say, drop it then the whole insert actually fails with error stating that insert failed because the statement tried to change the table schema. If the delete from #funny_test is replaced with select 1 then insert fails because it actually tries to accumulate all select(s) results on the hip and the select 1 portion does not have the value for the second column. But if delete from #funny_test is replaced with for example, select 99, 99 then insert succeeds and the total number of the records in the tables happens to be 15 (!), i.e. 10 original records, 4 inserted because of the first select of the DSql, and 1 inserted from the second select of DSql. In this case the ' select id, row_num from #funny_Test where row_num < ' + str(@Del_Row_Num) +'; select 99, 99;' is functionally equivalent to ' select id, row_num from #funny_Test where row_num < ' + str(@Del_Row_Num) + ' union all select 99, 99;'
Oleg
February 3, 2010 at 6:33 pm
I thoroughly enjoyed this question. After the raft of recent questions with debatable answers, this had no 'trick' and tested several concepts, ie:
1) Temp tables can be referenced by "lower level" code, whether that be an EXEC() or a stored proc
2) The lower level code will execute in it's entirety before it returns to the calling code
3) Results are stored in places outside of the referenced tables (ie: memory or tempdb, obviously)
4) Illustrates an example of SQL Injection
S.
February 3, 2010 at 11:28 pm
Good question. I guessed 0 thinking it was SQL injection issue which would delete all the rows in the table.Learnt sth new.
February 4, 2010 at 2:46 am
Interesting question, thanks 🙂
I got it right, but largely down to guesswork - my initial thought was 0, but the name of the temp table "#funny_test" made me expected something, well, funny - so I thought again!
February 4, 2010 at 5:34 am
Good One ...
Viewing 15 posts - 16 through 30 (of 46 total)
You must be logged in to reply to this topic. Login to reply