Representative query




  • Some minor anonymization happened, so things like [code]SELECT blah FROM [a] AS a[/code] doesn't count. Anyway, this is the final result of a ~2K LOC method that creates this query.

    <html><body style='color:#000000; background:#ffffff; '>
    CREATE TABLE #TempTable
    (
        [id]                INT           IDENTITY PRIMARY KEY,
        [a_id]              INT          ,
        [name]              VARCHAR (255),
        [city]              VARCHAR (255),
        [state]             VARCHAR (255),
        [province]          VARCHAR (255),
        [status_id]         INT          ,
        [modification_date] DATETIME     ,
        [country]           VARCHAR (255),
        [sample_count]      INT          ,
        employee_count      INT          ,
        overview            VARCHAR (MAX)
    );
    

    INSERT INTO #TempTable ([a_id], [name], [city], [state], [province], [status_id], [modification_date], [country], [sample_count], [employee_count], [overview])
    SELECT DISTINCT a.[a_id],
    a.[name],
    a.[city],
    state.[abbreviation] AS state_name,
    a.[province],
    a.status_id,
    a.[modification_date],
    country.[name] AS country,
    (SELECT COUNT(*)
    FROM a_client_work
    LEFT OUTER JOIN
    a_client
    ON a_client.a_client_id = a_client_work.a_client_id
    LEFT OUTER JOIN
    a
    ON a.a_id = a_client.a_id
    WHERE a.status_id = 3
    AND a.a_id = a.a_id) AS sample_count,
    a.employee_count,
    CONVERT (VARCHAR (MAX), a.overview)
    FROM [a] AS a
    LEFT OUTER JOIN
    [lkup_state] AS state
    ON state.[state_id] = a.[state_id]
    LEFT OUTER JOIN
    [lkup_country] AS country
    ON country.[country_id] = a.[country_id]
    WHERE a.[status_id] <> 0
    AND (a.a_id IN (SELECT a_id
    FROM a_attribute
    WHERE value_id = 136
    OR value_id = 137
    OR value_id = 138
    OR value_id = 11
    OR value_id = 10
    OR value_id = 14
    OR value_id = 16
    OR value_id = 13
    OR value_id = 139
    OR value_id = 140
    OR value_id = 22
    OR value_id = 142
    OR value_id = 144
    OR value_id = 152
    OR value_id = 5
    OR value_id = 18
    OR value_id = 145
    OR value_id = 90
    OR value_id = 146
    OR value_id = 141
    OR value_id = 147
    OR value_id = 20
    OR value_id = 148
    OR value_id = 149
    OR value_id = 153
    OR value_id = 150
    OR value_id = 109
    OR value_id = 143)
    OR a.a_id IN (SELECT a_id
    FROM a_client
    WHERE a_client_id IN (SELECT a_client_id
    FROM a_client_attribute
    WHERE value_id = 136
    OR value_id = 137
    OR value_id = 138
    OR value_id = 11
    OR value_id = 10
    OR value_id = 14
    OR value_id = 16
    OR value_id = 13
    OR value_id = 139
    OR value_id = 140
    OR value_id = 22
    OR value_id = 142
    OR value_id = 144
    OR value_id = 152
    OR value_id = 5
    OR value_id = 18
    OR value_id = 145
    OR value_id = 90
    OR value_id = 146
    OR value_id = 141
    OR value_id = 147
    OR value_id = 20
    OR value_id = 148
    OR value_id = 149
    OR value_id = 153
    OR value_id = 150
    OR value_id = 109
    OR value_id = 143)))
    AND (a.a_id IN (SELECT a_id
    FROM a_attribute
    WHERE value_id = 33)
    OR a.a_id IN (SELECT a_id
    FROM a_client
    WHERE a_client_id IN (SELECT a_client_id
    FROM a_client_attribute
    WHERE value_id = 33)))
    AND a.a_id IN (SELECT a_id
    FROM a
    WHERE state_id = 7
    OR state_id = 20
    OR state_id = 22
    OR state_id = 30
    OR state_id = 40
    OR state_id = 46
    OR state_id = 8
    OR state_id = 9
    OR state_id = 21
    OR state_id = 31
    OR state_id = 33
    OR state_id = 39
    OR state_id = 49
    OR state_id = 1
    OR state_id = 10
    OR state_id = 11
    OR state_id = 34
    OR state_id = 41
    OR state_id = 43
    OR state_id = 47
    OR state_id = 14
    OR state_id = 15
    OR state_id = 16
    OR state_id = 17
    OR state_id = 18
    OR state_id = 23
    OR state_id = 24
    OR state_id = 25
    OR state_id = 26
    OR state_id = 28
    OR state_id = 35
    OR state_id = 36
    OR state_id = 42
    OR state_id = 50
    OR state_id = 4
    OR state_id = 19
    OR state_id = 32
    OR state_id = 37
    OR state_id = 44
    OR state_id = 2
    OR state_id = 3
    OR state_id = 5
    OR state_id = 6
    OR state_id = 12
    OR state_id = 13
    OR state_id = 27
    OR state_id = 29
    OR state_id = 38
    OR state_id = 45
    OR state_id = 48
    OR state_id = 51
    OR state_id = 0
    OR state_id = 1
    OR state_id = 2
    OR state_id = 53
    OR state_id = 3
    OR state_id = 4
    OR state_id = 54
    OR state_id = 5
    OR state_id = 6
    OR state_id = 7
    OR state_id = 8
    OR state_id = 9
    OR state_id = 10
    OR state_id = 11
    OR state_id = 12
    OR state_id = 13
    OR state_id = 14
    OR state_id = 15
    OR state_id = 16
    OR state_id = 17
    OR state_id = 18
    OR state_id = 19
    OR state_id = 20
    OR state_id = 55
    OR state_id = 21
    OR state_id = 22
    OR state_id = 23
    OR state_id = 24
    OR state_id = 25
    OR state_id = 26
    OR state_id = 27
    OR state_id = 28
    OR state_id = 29
    OR state_id = 56
    OR state_id = 30
    OR state_id = 31
    OR state_id = 32
    OR state_id = 33
    OR state_id = 57
    OR state_id = 34
    OR state_id = 35
    OR state_id = 58
    OR state_id = 59
    OR state_id = 60
    OR state_id = 36
    OR state_id = 37
    OR state_id = 61
    OR state_id = 38
    OR state_id = 39
    OR state_id = 62
    OR state_id = 63
    OR state_id = 40
    OR state_id = 64
    OR state_id = 41
    OR state_id = 42
    OR state_id = 43
    OR state_id = 44
    OR state_id = 45
    OR state_id = 46
    OR state_id = 47
    OR state_id = 48
    OR state_id = 49
    OR state_id = 50
    OR state_id = 51
    OR state_id = 65)
    AND (a.postal_code IN (SELECT postal_code
    FROM market_postal_code
    WHERE market_id = 1
    OR market_id = 2
    OR market_id = 3
    OR market_id = 4
    OR market_id = 5
    OR market_id = 6
    OR market_id = 7
    OR market_id = 8
    OR market_id = 9
    OR market_id = 10
    OR market_id = 11
    OR market_id = 12
    OR market_id = 13
    OR market_id = 14
    OR market_id = 15
    OR market_id = 16
    OR market_id = 17
    OR market_id = 18
    OR market_id = 19))
    AND (a.a_id IN (SELECT a_id
    FROM a_attribute
    WHERE value_id = 120
    OR value_id = 121)
    OR a.a_id IN (SELECT a_id
    FROM a_client
    WHERE a_client_id IN (SELECT a_client_id
    FROM a_client_attribute
    WHERE value_id = 120
    OR value_id = 121)))
    AND (a.a_id IN (SELECT a_id
    FROM a_attribute
    WHERE value_id = 124
    OR value_id = 125
    OR value_id = 128
    OR value_id = 123
    OR value_id = 131
    OR value_id = 135)
    OR a.a_id IN (SELECT a_id
    FROM a_client
    WHERE a_client_id IN (SELECT a_client_id
    FROM a_client_attribute
    WHERE value_id = 124
    OR value_id = 125
    OR value_id = 128
    OR value_id = 123
    OR value_id = 131
    OR value_id = 135)))
    GROUP BY a.a_id, a.[name], a.city, state.[abbreviation], a.province, a.modification_date, a.status_id, country.[name], a.employee_count, CONVERT (VARCHAR (MAX), a.overview)
    ORDER BY a.status_id DESC, a.modification_date DESC;

    DECLARE @FirstRec AS INT,
    @LastRec AS INT;

    SELECT @TotalRows = (SELECT COUNT(*)
    FROM #TempTable);

    SELECT @FirstRec = (@CurrentPage - 1) * @PageSize;

    SELECT @LastRec = (@CurrentPage * @PageSize + 1);

    SELECT *
    FROM #TempTable
    WHERE ID > @FirstRec
    AND ID < @LastRec;

    DROP TABLE #TempTable;


  • Winner of the 2016 Presidential Election

    Is the WTF that they should have used

    value_id in ( 124, 125, 128, 123, 131, 135 )
    ?



  •  I love those bizarre unsorted lists. It checks for state_id equal to all the numbers from 1-51 twice...



  • No, the best part is all of those options are pulled from the query string.

    results.aspx?a_services=137%2c138%2c11%2c10%2c14%2c16%2c13%2c139%2c140%2c22%2c142%2c144%2c152%2c5%2c18%2c145%2c90%2c146%2c141%2c147%2c20%2c148%2c149%2c153%2c150%
    2c109%2c143&city=2%2c3%2c4%2c5%2c6%2c7%2c8%2c9%2c10%2c11%2c12%2c13%2c14%2c15%2c16%2c17%2c18%2c19&employees_max=1000&employees_min=0&focus=120&
    experience=77%2c31%2c67%2c76%2c25%2c28%2c29%2c32%2c69%2c71%2c79%2c33%2c75%2c74%2c105%2c36%2c35%2c104%2c34%2c37%2c70%2c119%2c38%2c30&
    region=61%2c62%2c63%2c64%2c65%2c66&search_type=advanced&segment=125%2c128%2c123%2c131%2c135&
    state=2%2c53%2c3%2c4%2c54%2c5%2c6%2c7%2c8%2c9%2c10%2c11%2c12%2c13%2c14%2c15%2c16%2c17%2c18%2c19%2c20%2c55%2c21%2c22%2c23%2c24%2c25%2c26%
    2c27%2c28%2c29%2c56%2c30%2c31%2c32%2c33%2c57%2c34%2c35%2c58%2c59%2c60%2c36%2c37%2c61%2c38%2c39%2c62%2c63%2c40%2c64%2c41%2c42%2c43%2c44%2c45%2c46%2c47%2c48%2c49%2c50%2c51%2c65


  • Winner of the 2016 Presidential Election

    @mikeTheLiar said:

    No, the best part is all of those options are pulled from the query string.

    results.aspx?a_services=137%2c138%2c11%2c10%2c14%2c16%2c13%2c139%2c140%2c22%2c142%2c144%2c152%2c5%2c18%2c145%2c90%2c146%2c141%2c147%2c20%2c148%2c149%2c153%2c150%
    2c109%2c143&city=2%2c3%2c4%2c5%2c6%2c7%2c8%2c9%2c10%2c11%2c12%2c13%2c14%2c15%2c16%2c17%2c18%2c19&employees_max=1000&employees_min=0&focus=120&
    experience=77%2c31%2c67%2c76%2c25%2c28%2c29%2c32%2c69%2c71%2c79%2c33%2c75%2c74%2c105%2c36%2c35%2c104%2c34%2c37%2c70%2c119%2c38%2c30&
    region=61%2c62%2c63%2c64%2c65%2c66&search_type=advanced&segment=125%2c128%2c123%2c131%2c135&
    state=2%2c53%2c3%2c4%2c54%2c5%2c6%2c7%2c8%2c9%2c10%2c11%2c12%2c13%2c14%2c15%2c16%2c17%2c18%2c19%2c20%2c55%2c21%2c22%2c23%2c24%2c25%2c26%
    2c27%2c28%2c29%2c56%2c30%2c31%2c32%2c33%2c57%2c34%2c35%2c58%2c59%2c60%2c36%2c37%2c61%2c38%2c39%2c62%2c63%2c40%2c64%2c41%2c42%2c43%2c44%2c45%2c46%2c47%2c48%2c49%2c50%2c51%2c65

    WTF! Obviously that needs to be base-64 encoded.


  • @mikeTheLiar said:

    No, the best part is all of those options are pulled from the query string.

    results.aspx?a_services=137%2c138%2c11%2c10%2c14%2c16%2c13%2c139%2c140%2c22%2c142%2c144%2c152%2c5%2c18%2c145%2c90%2c146%2c141%2c147%2c20%2c148%2c149%2c153%2c150%
    2c109%2c143&city=2%2c3%2c4%2c5%2c6%2c7%2c8%2c9%2c10%2c11%2c12%2c13%2c14%2c15%2c16%2c17%2c18%2c19&employees_max=1000&employees_min=0&focus=120&
    experience=77%2c31%2c67%2c76%2c25%2c28%2c29%2c32%2c69%2c71%2c79%2c33%2c75%2c74%2c105%2c36%2c35%2c104%2c34%2c37%2c70%2c119%2c38%2c30&
    region=61%2c62%2c63%2c64%2c65%2c66&search_type=advanced&segment=125%2c128%2c123%2c131%2c135&
    state=2%2c53%2c3%2c4%2c54%2c5%2c6%2c7%2c8%2c9%2c10%2c11%2c12%2c13%2c14%2c15%2c16%2c17%2c18%2c19%2c20%2c55%2c21%2c22%2c23%2c24%2c25%2c26%
    2c27%2c28%2c29%2c56%2c30%2c31%2c32%2c33%2c57%2c34%2c35%2c58%2c59%2c60%2c36%2c37%2c61%2c38%2c39%2c62%2c63%2c40%2c64%2c41%2c42%2c43%2c44%2c45%2c46%2c47%2c48%2c49%2c50%2c51%2c65

    At least your are lucky it's ASP.Net and you use a decent data client library. If this was PHP there would be no records returned at all, the result set would only include the message "X row(s) affected" because there is no call to SET NOCOUNT in the batch; the driver would only return the first message (which is the number of rows inserted in the temp table at the beginning).

    I know this, and yet once in a while I get caught by this "feature" and waste time trying to debug a non-broken stored procedure. Well that's PHP.



  • @mikeTheLiar said:

    No, the best part is all of those options are pulled from the query string.

    Generated code has a sort of get out of jail free card, which is kind of what this query is. Seems like the WTFs should involve potential SQL injections or just something really stupid that the queries do. That query is totally tl;dr, so you'll have to tell us if the query itself is worthless or whatever.



  • @boomzilla said:

    Generated code has a sort of get out of jail free card,
     

    Ridiculous! I demand equal rights for AI!



  • @boomzilla said:

    @mikeTheLiar said:
    No, the best part is all of those options are pulled from the query string.

    Generated code has a sort of get out of jail free card, which is kind of what this query is. Seems like the WTFs should involve potential SQL injections or just something really stupid that the queries do. That query is totally tl;dr, so you'll have to tell us if the query itself is worthless or whatever.

    Alright, we'll go deeper. Really all I was doing was poking fun at the fact that this query is entirely illegible and could be done in a much better way. FWIW, this is what made me give up on this job and start contacting recruiters.

    This actually is injection vulnerable, in a very stupid way. The basic idea is an advanced search page, with a whole bunch of check boxes of various categories, and a "min # employees" and "max # employees" text boxes.

    First, the query string is generated by something called a [code]UrlParameterPasser[/code]. WTF is that? Whatever it is, it inherits from [code]public abstract class BaseParameterPasser[/code]. There's a lot of really weird, unnecessary inheritance handwaving and reflection and shit like that, things like:

    [code]
    void doSomeThing(string s)
    {
        base.doSomeOtherThingEntirely(s);
    }
    [/code]
    
    Anyway, long story short, it loops over the search parameters, UrlEncodes the values, and creates a query string. Then it does the same thing in reverse, later, when this monstrosity of a url gets turned into SQL (at least, that's the idea, it doesn't really work all that well). Anyway, the injection comes in when actually creating the above query, which looks like this:

    [code]
    try
    {
         //bunch of query creation
    }
    catch(Exception e)
    {
         SendError(e.Message);
    }
    // ignore exception, keep going
    [/code]
    

    So, you can put whatever you want into the min/max fields [for example: ) OR 1 = 1; -- ], the code barfs on the non-int value, and just keeps going. I get an email that just says: "CAPTURED ERROR: Failed to convert parameter value from a String to a Int32" and that's it.



  • @dhromed said:

    @boomzilla said:

    Generated code has a sort of get out of jail free card,
     

    Ridiculous! I demand equal rights for AI!


    This isn't AI, this is AWTF.



  • @Ronald said:

    @mikeTheLiar said:

    No, the best part is all of those options are pulled from the query string.

    results.aspx?a_services=137%2c138%2c11%2c10%2c14%2c16%2c13%2c139%2c140%2c22%2c142%2c144%2c152%2c5%2c18%2c145%2c90%2c146%2c141%2c147%2c20%2c148%2c149%2c153%2c150%
    2c109%2c143&city=2%2c3%2c4%2c5%2c6%2c7%2c8%2c9%2c10%2c11%2c12%2c13%2c14%2c15%2c16%2c17%2c18%2c19&employees_max=1000&employees_min=0&focus=120&
    experience=77%2c31%2c67%2c76%2c25%2c28%2c29%2c32%2c69%2c71%2c79%2c33%2c75%2c74%2c105%2c36%2c35%2c104%2c34%2c37%2c70%2c119%2c38%2c30&
    region=61%2c62%2c63%2c64%2c65%2c66&search_type=advanced&segment=125%2c128%2c123%2c131%2c135&
    state=2%2c53%2c3%2c4%2c54%2c5%2c6%2c7%2c8%2c9%2c10%2c11%2c12%2c13%2c14%2c15%2c16%2c17%2c18%2c19%2c20%2c55%2c21%2c22%2c23%2c24%2c25%2c26%
    2c27%2c28%2c29%2c56%2c30%2c31%2c32%2c33%2c57%2c34%2c35%2c58%2c59%2c60%2c36%2c37%2c61%2c38%2c39%2c62%2c63%2c40%2c64%2c41%2c42%2c43%2c44%2c45%2c46%2c47%2c48%2c49%2c50%2c51%2c65

    At least your are lucky it's ASP.Net and you use a decent data client library. If this was PHP there would be no records returned at all, the result set would only include the message "X row(s) affected" because there is no call to SET NOCOUNT in the batch; the driver would only return the first message (which is the number of rows inserted in the temp table at the beginning).

    I know this, and yet once in a while I get caught by this "feature" and waste time trying to debug a non-broken stored procedure. Well that's PHP.

    Well, to be fair, that's only a problem if you don't use multi-query or you forget to cycle through and dispose all your result sets. But as a bonus, in the latter case, your whole connection stops working, so theres that.


    Unless you're talking about some other situation that I've never seen.



  • @kilroo said:

    @Ronald said:
    @mikeTheLiar said:

    No, the best part is all of those options are pulled from the query string.

    results.aspx?a_services=137%2c138%2c11%2c10%2c14%2c16%2c13%2c139%2c140%2c22%2c142%2c144%2c152%2c5%2c18%2c145%2c90%2c146%2c141%2c147%2c20%2c148%2c149%2c153%2c150%
    2c109%2c143&city=2%2c3%2c4%2c5%2c6%2c7%2c8%2c9%2c10%2c11%2c12%2c13%2c14%2c15%2c16%2c17%2c18%2c19&employees_max=1000&employees_min=0&focus=120&
    experience=77%2c31%2c67%2c76%2c25%2c28%2c29%2c32%2c69%2c71%2c79%2c33%2c75%2c74%2c105%2c36%2c35%2c104%2c34%2c37%2c70%2c119%2c38%2c30&
    region=61%2c62%2c63%2c64%2c65%2c66&search_type=advanced&segment=125%2c128%2c123%2c131%2c135&
    state=2%2c53%2c3%2c4%2c54%2c5%2c6%2c7%2c8%2c9%2c10%2c11%2c12%2c13%2c14%2c15%2c16%2c17%2c18%2c19%2c20%2c55%2c21%2c22%2c23%2c24%2c25%2c26%
    2c27%2c28%2c29%2c56%2c30%2c31%2c32%2c33%2c57%2c34%2c35%2c58%2c59%2c60%2c36%2c37%2c61%2c38%2c39%2c62%2c63%2c40%2c64%2c41%2c42%2c43%2c44%2c45%2c46%2c47%2c48%2c49%2c50%2c51%2c65

    At least your are lucky it's ASP.Net and you use a decent data client library. If this was PHP there would be no records returned at all, the result set would only include the message "X row(s) affected" because there is no call to SET NOCOUNT in the batch; the driver would only return the first message (which is the number of rows inserted in the temp table at the beginning).

    I know this, and yet once in a while I get caught by this "feature" and waste time trying to debug a non-broken stored procedure. Well that's PHP.

    Well, to be fair, that's only a problem if you don't use multi-query or you forget to cycle through and dispose all your result sets. But as a bonus, in the latter case, your whole connection stops working, so theres that.


    Unless you're talking about some other situation that I've never seen.

    Typically if you have a stored procedure that does a bunch of insert/update then a select, you would expect the stored procedure to return a single result set (the select). That's how the ADO.NET driver will interpret the situation (it will ignore DONE_IN_PROC messages), but for some reason the PHP drivers (all of thems) don't and will interpret those "rows affected" messages as result sets. Which is annoying because the whole purpose* of a stored procedure is to encapsulate a data operation, but if you use PHP you have to know what is happening inside the stored procedure in order to process its result.

    * maybe not the whole purpose but that's a common use case


  • Discourse touched me in a no-no place

    @Ronald said:

    Typically if you have a stored procedure that does a bunch of insert/update then a select, you would expect the stored procedure to return a single result set (the select). That's how the ADO.NET driver will interpret the situation (it will ignore DONE_IN_PROC messages), but for some reason the PHP drivers (all of thems) don't and will interpret those "rows affected" messages as result sets. Which is annoying because the whole purpose* of a stored procedure is to encapsulate a data operation, but if you use PHP you have to know what is happening inside the stored procedure in order to process its result.
    Stop ragging on PHP! It's not its fault that the language creators are fucking morons who need to die slowly in a fire for being incompetent jerkwads.



  • @dkf said:

    @Ronald said:
    Typically if you have a stored procedure that does a bunch of insert/update then a select, you would expect the stored procedure to return a single result set (the select). That's how the ADO.NET driver will interpret the situation (it will ignore DONE_IN_PROC messages), but for some reason the PHP drivers (all of thems) don't and will interpret those "rows affected" messages as result sets. Which is annoying because the whole purpose* of a stored procedure is to encapsulate a data operation, but if you use PHP you have to know what is happening inside the stored procedure in order to process its result.
    Stop ragging on PHP! It's not its fault that the language creators are fucking morons who need to die slowly in a fire for being incompetent jerkwads.

    Once I had to implement the SessionHandlerInterface to store sessions data in a database and this provided me with the rare pleasure to observe the way sessions are serialized in PHP. Of course this is not done using the serialize() method but with one of two mechanisms: either an internal method (which uses a weird pipe-delimited JSONish format) or the superb WDDX format (if the module is available). You know, in case you want to share your sessions with ColdFusion pages.


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.