InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 18:43:49 GMT20http://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspx[quote][b]Peter Trast (3/30/2010)[/b][hr][quote][b]Paul White NZ (3/30/2010)[/b][hr]This question made my head hurt.[/quote]Isn't that normal?[/quote]I suppose it is - though for varying reasons. This one was good in that it was thinking-related pain, not banging-my-head-against-the-wall-in-frustration pain.Tue, 30 Mar 2010 13:51:51 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspx[quote][b]Paul White NZ (3/30/2010)[/b][hr]This question made my head hurt.[/quote]Isn't that normal?Tue, 30 Mar 2010 13:44:10 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxThis question made my head hurt.Tue, 30 Mar 2010 09:13:09 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxWow! The shocking thing to me is that I understood it and answered it right! Those T-SQL classes that I am teaching are starting to pay off ;-)Wed, 03 Mar 2010 11:32:47 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxExcellent QotD. Grey matter exercise, rather than spot the illegal statement :)Tue, 16 Feb 2010 14:00:23 GMTantony-688446http://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxBest QOTD I've seen for a while. It forced me to think.Tue, 16 Feb 2010 12:42:58 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspx[quote][b]Oleg Netchaev (2/5/2010)[/b][hr][quote][b]JosephDMarsh (2/5/2010)[/b][hr]I chose 4, but (as it turns out) I was guessing. (I thought I knew what was going on, chose an answer and clicked 'Submit' before actually studying the question -- yes, that's a problem I'm working on)After re-reading the explanation, as well as all of the posts in this thread, I'm not clear on what is happening exactly.As a Rookie, this is what I *think* is happening:1. Temp table is created2. 10 records are created in temp table3. 4 More records are added to temp table (first part of @sql_str)4. All records are deleted from temp table (second part of @sql_str)Obviously, that is not the case. I can't find anything in BOL to help figger this out. Any help?Thanks in advance,- Joseph Marsh[/quote]Joseph,You are correct, but step 3 needs tweaking and last important step is missing. Here is what is happening:1. Temp table is created2. 10 records are inserted into the temp table3. The dynamic sql is examined and then executed by the engine. Since the first part of the dynamic sql selects 4 records, those are placed on the heap (saved in memory) in order to be returned back when needed. They cannot be returned back as of yet because the dynamic sql has second part.4. All records (10 to be exact) are deleted from the temp table.5. insert into part now kicks in and what the engine sees at this point is to execute the following: insert into temp table select whatever was selected and saved on the heap from the executing dynamic sql, which happens to be select first 4 records from temp table. Thus, the 4 originally selected records are inserted back into the temp table after every row has just been deleted from it. 4 records inserted into empty table make the table now have 4 records.Hope this helps.This is why I mentioned in my earlier post that if you were to replace the [b]delete from temp table [/b](second portion of the dynamic sql) with something like [b]select 99, 99[/b] then results of the first select (4 records) will be inserted and the results of the second select (one record) will be inserted as well and the result will then be 15 records in the temp table. And if you were to replace the [b]delete from temp table [/b]part with the select of a different shape (something like [b]select 1[/b]) then nothing will be inserted because the second select will return data not compatible with expected shape (2 columns per record returned).OlegOleg[/quote]Good question, and an excellent explanation.Mon, 08 Feb 2010 02:08:47 GMThakan.wintherhttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxAH -- now I get it (thank you Oleg and Gennadiy). Here's the source of my confusion: I was thinking that [b]this[/b] ...[i]insert into #funny_Test exec(@Sql_str)[/i]... resolved into [b]this[/b] ...[i]insert into #funny_Test select id, row_num from #funny_Test where row_num < 5delete from #funny_Test[/i]... which would INSERT the 4 records first, [b]then[/b] delete all of the records in the table.BUT, now I understand that while the SELECT statement [b]does[/b] execute and return records, the INSERT statement doesn't get that data and insert it until after the DELETE statement executes.Very Nice. This is my "New Thing I Learned Today". And it's not even noon yet (where I am)!Fri, 05 Feb 2010 09:00:32 GMTJoseph D. Marshhttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspx[quote][b]JosephDMarsh (2/5/2010)[/b][hr]I chose 4, but (as it turns out) I was guessing. (I thought I knew what was going on, chose an answer and clicked 'Submit' before actually studying the question -- yes, that's a problem I'm working on)After re-reading the explanation, as well as all of the posts in this thread, I'm not clear on what is happening exactly.As a Rookie, this is what I *think* is happening:1. Temp table is created2. 10 records are created in temp table3. 4 More records are added to temp table (first part of @sql_str)4. All records are deleted from temp table (second part of @sql_str)Obviously, that is not the case. I can't find anything in BOL to help figger this out. Any help?Thanks in advance,- Joseph Marsh[/quote]Joseph,You are correct, but step 3 needs tweaking and last important step is missing. Here is what is happening:1. Temp table is created2. 10 records are inserted into the temp table3. The dynamic sql is examined and then executed by the engine. Since the first part of the dynamic sql selects 4 records, those are placed on the heap (saved in memory) in order to be returned back when needed. They cannot be returned back as of yet because the dynamic sql has second part.4. All records (10 to be exact) are deleted from the temp table.5. insert into part now kicks in and what the engine sees at this point is to execute the following: insert into temp table select whatever was selected and saved on the heap from the executing dynamic sql, which happens to be select first 4 records from temp table. Thus, the 4 originally selected records are inserted back into the temp table after every row has just been deleted from it. 4 records inserted into empty table make the table now have 4 records.Hope this helps.This is why I mentioned in my earlier post that if you were to replace the [b]delete from temp table [/b](second portion of the dynamic sql) with something like [b]select 99, 99[/b] then results of the first select (4 records) will be inserted and the results of the second select (one record) will be inserted as well and the result will then be 15 records in the temp table. And if you were to replace the [b]delete from temp table [/b]part with the select of a different shape (something like [b]select 1[/b]) then nothing will be inserted because the second select will return data not compatible with expected shape (2 columns per record returned).OlegOlegFri, 05 Feb 2010 08:49:26 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspx[quote][b]JosephDMarsh (2/5/2010)[/b][hr]I chose 4, but (as it turns out) I was guessing. (I thought I knew what was going on, chose an answer and clicked 'Submit' before actually studying the question -- yes, that's a problem I'm working on)After re-reading the explanation, as well as all of the posts in this thread, I'm not clear on what is happening exactly.As a Rookie, this is what I *think* is happening:1. Temp table is created2. 10 records are created in temp table3. 4 More records are added to temp table (first part of @sql_str)4. All records are deleted from temp table (second part of @sql_str)Obviously, that is not the case. I can't find anything in BOL to help figger this out. Any help?Thanks in advance,- Joseph Marsh[/quote]Some corrections:3. 4 rows selected and save somewhere in SQL buffer as a result set4. All records are deleted from temp table (second part of @sql_str)5. 4 rows from result set generated on step 3 re-inserted (as a result of D-SQL batch execution)To understand it better just imagine that D-SQL is executed as a Stored Procedure (or could be)insert into <table>exec <SP with 2 statements>Regards,GennadiyFri, 05 Feb 2010 08:44:22 GMTgchornenkyyhttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxI chose 4, but (as it turns out) I was guessing. (I thought I knew what was going on, chose an answer and clicked 'Submit' before actually studying the question -- yes, that's a problem I'm working on)After re-reading the explanation, as well as all of the posts in this thread, I'm not clear on what is happening exactly.As a Rookie, this is what I *think* is happening:1. Temp table is created2. 10 records are created in temp table3. 4 More records are added to temp table (first part of @sql_str)4. All records are deleted from temp table (second part of @sql_str)Obviously, that is not the case. I can't find anything in BOL to help figger this out. Any help?Thanks in advance,- Joseph MarshFri, 05 Feb 2010 08:29:52 GMTJoseph D. Marshhttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspx[quote][b]sqlusers (2/5/2010)[/b][hr]Good Question. Thanks...But, can you tell me how to delete the "@Sql_str" string value without restarting the sql services?[/quote]It's a temporary variable that gets automatically deleted when the client session ends.Is that what you meant?Fri, 05 Feb 2010 08:27:33 GMTKelsey Thorntonhttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspx[quote][b]sqlusers (2/5/2010)[/b][hr]Good Question. Thanks...But, can you tell me how to delete the "@Sql_str" string value without restarting the sql services?[/quote]Did you mean something like that?Assign NULL value:select @Sql_str = null or balnk / empty string:select @Sql_str = '' Regards,GennadiyFri, 05 Feb 2010 06:30:41 GMTgchornenkyyhttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxI liked the question, even though I got it wrong.I answered 10 because I misunderstood how the row_num was going to work. I have been working with row_num quite a lot recently where I group by to match duplicates, so i was thinking it was a trick question where each row would have a row_num of 1 because each object_id was different, therefore it wouldn't count up, and all records would then qualify in the later statement, but this wasn't the case. I did catch the rest of it though :)PaulFri, 05 Feb 2010 03:46:55 GMTpaul.goldstrawhttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxGood Question. Thanks...But, can you tell me how to delete the "@Sql_str" string value without restarting the sql services?Fri, 05 Feb 2010 03:13:35 GMTsqlusershttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspx[quote][b]honza.mf (2/3/2010)[/b][hr]Very nice question. Nice SQL inject with a side effect.I hope I will never use something like this one.[/quote]I agree with both sentiments!It was a tricky question, made a [b]little[/b] easier if you paste the command into a context-sensitive editor/Query toolFri, 05 Feb 2010 01:06:35 GMTKelsey Thorntonhttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxVery good question. You can find some documentation supporting this behavior here:[url]http://msdn.microsoft.com/en-us/library/aa933206(SQL.80).aspx[/url] Specifically:[quote]If [i]execute_statement[/i] is used with INSERT, [b]each result set[/b] (my emphasis) must be compatible with the columns in the table or in [i]column_list[/i].[/quote]While not saying it outright, that implies that the result set of each statement executed is returned to be inserted.Thu, 04 Feb 2010 12:55:17 GMTsknoxhttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxGood One ...Thu, 04 Feb 2010 05:34:29 GMTBhavesh_Patelhttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxInteresting 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!Thu, 04 Feb 2010 02:46:06 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxGood question. I guessed 0 thinking it was SQL injection issue which would delete all the rows in the table.Learnt sth new.Wed, 03 Feb 2010 23:28:30 GMTbdbahttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxI 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 proc2) The lower level code will execute in it's entirety before it returns to the calling code3) Results are stored in places outside of the referenced tables (ie: memory or tempdb, obviously)4) Illustrates an example of SQL InjectionS.Wed, 03 Feb 2010 18:32:59 GMTFalhttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxIt 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 [b]delete from #funny_test [/b]is replaced with [b]select 1 [/b]then insert fails because it actually tries to accumulate all select(s) results on the hip and the [b]select 1[/b] portion does not have the value for the second column. But if [b]delete from #funny_test [/b]is replaced with for example, [b]select 99, 99[/b] 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;' OlegWed, 03 Feb 2010 12:24:25 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxI hadn't considered the SQL Injection aspect of this question. Thanks for pointing that out.Wed, 03 Feb 2010 12:15:55 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxThank 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,GennadiyWed, 03 Feb 2010 10:42:33 GMTgchornenkyyhttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxThis 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 [b]insert into[/b] 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 [b]insert into[/b] 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!OlegWed, 03 Feb 2010 10:21:56 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspx[quote][b]SanjayAttray (2/3/2010)[/b][hr]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.[/quote]What would have running it in SSMS and putting the answer in have achieved apart from getting you the points?Wed, 03 Feb 2010 08:32:18 GMTLongyhttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxVery good question. I should had executed it on SSMS and then answered it. But, no I just read and answered and got it wrong.Wed, 03 Feb 2010 08:06:36 GMTSanjayAttrayhttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxThanks for a good question. For some reason, I was thinking that what would be inserted would be the deletion of the table.Wed, 03 Feb 2010 07:38:47 GMTSteve Eckharthttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxI 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.Wed, 03 Feb 2010 06:45:25 GMTRonald Hhttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxnice question sirWed, 03 Feb 2010 06:11:16 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxAn 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. [i]Of course[/i] 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! :-)Wed, 03 Feb 2010 05:39:35 GMTronmoseshttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxVery nice question. Nice SQL inject with a side effect.I hope I will never use something like this one.Wed, 03 Feb 2010 04:32:20 GMThonza.mfhttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspx[quote][b]Simon Liddle (2/3/2010)[/b][hr][quote][b]Joy Smith San (2/3/2010)[/b][hr]Wel, Usualy I answer the question by reading it on the screen itself. I never copy and paste it in query analyzer.Had I copied it in query analyzer and read I would have definetly give right answer. Hence I dint feel it's a good question.[/quote]I don't understand how your reliance on reading the code in QA to get it right has any bearing on the question being good or not....:unsure: How would reading it in QA (I assume you do mean reading and not executing) have caused you to work out a different answer?[/quote]Yes, you are right. I dont execute and just read it.The difference is that, as you know when you copy it in QA color changes. I would have easily found that delete statement was a string appended to the insert statement.Anyways, I was just saying my opinion. Hope I have the freedom to post what I feel, instead of simply praising always.And yes, probably for a beginner it might be a good question. I agree.Wed, 03 Feb 2010 03:55:18 GMTJoy Smith Sanhttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspx[quote][b]Ninja's_RGR'us (2/3/2010)[/b][hr][quote][b]Joy Smith San (2/3/2010)[/b][hr]Wel, Usualy I answer the question by reading it on the screen itself. I never copy and paste it in query analyzer.Had I copied it in query analyzer and read I would have definetly give right answer. Hence I dint feel it's a good question.[/quote]So good questions are only the ones you can answer???[/quote]I dint mean it. I mean to say, this question is just tricky.Playing with words and nothing else.Wed, 03 Feb 2010 03:47:45 GMTJoy Smith Sanhttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspx[quote][b]Simon Liddle (2/3/2010)[/b][hr]I thought that the dynamic SQL would execute and the results from the last statement - the delete statement would be inserted.[/quote]...and that is nonsense the more I think about it! :)Wed, 03 Feb 2010 03:45:29 GMTSimon Liddlehttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxReally This is not easy for sql newbies .Tricky questionBut You Can Learn more knowledge by thisWed, 03 Feb 2010 03:37:01 GMTSree Arjun Divhttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxInteresting question - I guessed 0 rows - I thought that the dynamic SQL would execute and the results from the last statement - the delete statement would be inserted. Not really tried anything like this before - going to have a play with executing multiple statements in EXEC() and find out how it affects other things like @@rowcount as well. :-)Wed, 03 Feb 2010 03:35:35 GMTSimon Liddlehttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspx[quote][b]Joy Smith San (2/3/2010)[/b][hr]Wel, Usualy I answer the question by reading it on the screen itself. I never copy and paste it in query analyzer.Had I copied it in query analyzer and read I would have definetly give right answer. Hence I dint feel it's a good question.[/quote]I don't understand how your reliance on reading the code in QA to get it right has any bearing on the question being good or not....:unsure: How would reading it in QA (I assume you do mean reading and not executing) have caused you to work out a different answer?Wed, 03 Feb 2010 03:32:38 GMTSimon Liddlehttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspx[quote][b]Joy Smith San (2/3/2010)[/b][hr]Wel, Usualy I answer the question by reading it on the screen itself. I never copy and paste it in query analyzer.Had I copied it in query analyzer and read I would have definetly give right answer. Hence I dint feel it's a good question.[/quote]So good questions are only the ones you can answer???Wed, 03 Feb 2010 02:32:08 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic858374-1366-1.aspxTricky question, but not good for learning...good for dry runWed, 03 Feb 2010 02:29:40 GMTarup chakraborty