Post reply

Commenting in Dynamic query

  • August 3, 2012 at 9:00 am

    #1521448

    Thanks for a straightforward question.

    I found it quite easy because it's one of the opened doors for SQL injection.

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • August 3, 2012 at 9:01 am

    #1521449

    You missed the parenthesis surrounding the (@str1)

  • August 3, 2012 at 9:03 am

    #1521455

    vk-kirov (8/3/2012)

    I got a point for choosing the "correct" answer because I hadn't counted characters in the dynamic queries. Now I want an extra point for that reason. This is why I'm posting this.

    If you had counted them (and counted correctly) you would have dropped 4 characters and got the same answer as you got without counting. The question appears to be perfectly OK, all the complaints are from people who either can't count or are not good copy typists (hey didn't type the string in the question, they typed something with a different length). :hehe:

    Tom

  • August 3, 2012 at 9:05 am

    #1521456

    When I change the size for @str4 from 45 to 50 and ran a print LEN(@str4) I got a 48 not 49, which is the number of characters I count manually twice, this leaves a single - at the end which results in a syntax error. The execute does in fact work for a varchar length of 48 or greater. As stated before 45 is a really strange length.

  • August 3, 2012 at 9:08 am

    #1521461

    tommyh (8/3/2012)

    Unless i wrote it off wrong... the answer is wrong.

    create table #tmpTable (Fld1 char(1))

    insert into #tmpTable values ('1')

    declare @str1 varchar(45)

    declare @str2 varchar(45)

    declare @str3 varchar(45)

    declare @str4 varchar(45)

    set @str1 = 'select * from #tmpTable -- where Fld1 = 0'

    set @str2 = 'select * from #tmpTable --' + char(13) + 'where Fld1 = 0'

    set @str3 = 'select * from #tmpTable /*' + char(13) + 'where Fld1 = 0 */'

    set @str4 = 'select * from #tmpTable --/*' + char(13) + 'where Fld1 = 0 --*/'

    You typed it wrong. You typed 'where' instead of ' where' in three lines. It only matters in the last one of those.

    Now why on earth anyone would want to put comments in dynamic SQL is beyond me. Its hard enough getting all the little ' right and to introduce something into the string that doesnt need to be there... god no.

    There is sometimes good cause for including comments in dynamic SQL. Someone who comes along afterwards and is trying to fix a problem or do an enhancement can benefit from those comments (provided he's willing to print the SQL string so that he can read them).

    edit - fix broken quote tag

    Tom

  • August 3, 2012 at 9:19 am

    #1521465

    Lon-860191 (8/3/2012)

    When I change the size for @str4 from 45 to 50 and ran a print LEN(@str4) I got a 48 not 49, which is the number of characters I count manually twice, this leaves a single - at the end which results in a syntax error. The execute does in fact work for a varchar length of 48 or greater. As stated before 45 is a really strange length.

    Are you sure that you had the correct string? Or did you make the same error as Tommyh (who, being perhaps less arrogant that some others, published the string he had typed and said the question was only wrong if he's typed it right). He had missed the space before "where". Missing that does indeed give you 48, but the space was in the question so 48 was not the correct count. Perhaps you too omitted that space?

    You'll see that in my first post I included the string I had counted (and also checked using LEN, since so many people were getting a shorter string), which was, I believe (having checked very carefully), the string expression in the question. If it isn't, people can point out where the difference between teh expression in the question and the expression in my post is, and I will learn from that.

    Tom

  • August 3, 2012 at 9:34 am

    #1521476

    It's hard to tell when the SQL script is an image.

    I'm sure this will come down to some kind of argument, but when I see the image

    I don't see the space before the where clause.

    Because

    ' where'

    or 'where'

    both don't look exactly like that where clause, at least to me.

    To me there doesn't appear to be a space between the ' and the word where.


    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • August 3, 2012 at 9:41 am

    #1521481

    I don't see it either and that is what causes the error.

  • August 3, 2012 at 9:55 am

    #1521494

    I've edited the question down since I think it was a good question. I didn't catch the typo (grrr, no more images).

    I'll award points back.

  • August 3, 2012 at 10:06 am

    #1521502

    The fourth statement is 48 characters long, hence when stored in a varchar(45) variable, it is truncated at character 45 resulting in a syntax error due to the last character being a '-'.

    The correct answer is 1,0,1,error

  • August 3, 2012 at 10:08 am

    #1521504

    Well I got it right but there is a problem with the edited version. On the page in the email I received this morning the sql has statements like

    Set @str1 = 'select * from #tmpTable -- where Fld1 = 0’

    And the SQL in the page where we answer says

    SET @str1 = 'select count(*) from #tmpTable -- where Fld1 =0'

    A bit confusing since I looked at the first set I saw in the daily email and then clicked to answer and it was different. Knowing that this can be tricky I reconsidered and did get it but really had to look close for a Friday. Thanks for making us think. 😉

    M.

    Not all gray hairs are Dinosaurs!

  • August 3, 2012 at 10:22 am

    #1521511

    mtassin (8/3/2012)

    It's hard to tell when the SQL script is an image.

    I'm sure this will come down to some kind of argument, but when I see the image

    I don't see the space before the where clause.

    Because

    ' where'

    or 'where'

    both don't look exactly like that where clause, at least to me.

    To me there doesn't appear to be a space between the ' and the word where.

    You could be right. I see a space there, but on more careful study of the image I'm far from sure. :ermm:

    In fact (looking at the relative widths of w,n,and l in that image it's clear that it's a fixed spacing font, so I've made a thorough fool of myself - if it's in a fixed spacing font, there isn't any space! :blush: And SSMS uses a fixed spacing font for the query window, so I should have expected it to be a fixed spacing font :blush: :blush: because the question was probably put together using an SSMS query window.

    Apologies to all those I've accused of miscounting or miscopying - it's me that counted & copied wrong, not you. :blush: :blush: :blush: :blush: :blush:

    Tom

  • August 3, 2012 at 10:30 am

    #1521516

    For what this question was intending to do, it was a good question. Too bad about the string length issue causing controversy.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • August 3, 2012 at 1:48 pm

    #1521623

    L' Eomot Inversé (8/3/2012)

    You could be right. I see a space there, but on more careful study of the image I'm far from sure. :ermm:

    In fact (looking at the relative widths of w,n,and l in that image it's clear that it's a fixed spacing font, so I've made a thorough fool of myself - if it's in a fixed spacing font, there isn't any space! :blush: And SSMS uses a fixed spacing font for the query window, so I should have expected it to be a fixed spacing font :blush: :blush: because the question was probably put together using an SSMS query window.

    Apologies to all those I've accused of miscounting or miscopying - it's me that counted & copied wrong, not you. :blush: :blush: :blush: :blush: :blush:

    The worst thing with this question is that one can make more variants of spaces. Trimming the string leads to several ending variants:

    where Fld1 =0

    where Fld1 =0 -

    where Fld1 =0 --

    First and third variants are OK, second is bad.

    Unfortunately nobody will have the most interesting combination finished with "where Fld1 =0 --*/".

    Why not use in such a question varchar(max) variables? It eliminates SQL Server 2000, but it's clear nobody has to count characters.


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

  • August 4, 2012 at 7:26 am

    #1521701

    Hello; nice question indeed.:-)

    The below code which I have copied/written from the QotD email which I received today and this gives me the result as "1,error error, error" due to the variable length (under SQL2K8 R2)

    CREATE TABLE #tmpTable(Fld1 CHAR(1))

    INSERT INTO #tmptable VALUES ('1')

    DECLARE @str1 varchar(45)

    DECLARE @str2 varchar(45)

    DECLARE @str3 varchar(45)

    DECLARE @str4 varchar(45)

    SET @str1 = 'select count(*) from #tmpTable -- where Fld1 =0'

    SET @str2 = 'select count(*) from #tmpTable --' + CHAR(13) + 'where Fld1 =0'

    SET @str3 = 'select count(*) from #tmpTable /*' + CHAR(13) + 'where Fld1 =0 */'

    SET @str4 = 'select count(*) from #tmpTable --/*' + CHAR(13) + 'where Fld1 =0 --*/'

    EXECUTE(@str1)

    EXECUTE(@str2)

    EXECUTE(@str3)

    EXECUTE(@str4)

    DROP TABLE #tmpTable

    I see now the size is increased by 5 chars in each and the result is "1,0,1,0".

    But in the SQL 2000 the interpretation is different and it gives me all 1. (after increasing to var length to 50)

    I couldn't find the difference for these 2 versions on why the EXECUTE is executed with different results.

    Anyone tested this in 2012 and is this giving same result as 2008 R or 2000?

    🙂

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Viewing 15 posts - 31 through 45 (of 55 total)

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