Multi-statement execution

  • Very nice question. Nice SQL inject with a side effect.

    I hope I will never use something like this one.



    See, understand, learn, try, use efficient
    © Dr.Plch

  • 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

  • nice question sir

  • 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

  • Thanks for a good question. For some reason, I was thinking that what would be inserted would be the deletion of the table.


    Steve Eckhart

  • 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.

  • 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?

  • 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

  • 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

  • I hadn't considered the SQL Injection aspect of this question. Thanks for pointing that out.

  • 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

  • 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.

  • Good question. I guessed 0 thinking it was SQL injection issue which would delete all the rows in the table.Learnt sth new.

  • 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!

  • Good One ...



    [font="System"]Bhavesh Patel[/font]

    http://bhaveshgpatel.wordpress.com/

Viewing 15 posts - 16 through 30 (of 46 total)

You must be logged in to reply to this topic. Login to reply