1. Home
  2. Categories
  3. Side Bar WTF
  4. Representative query

Representative query

  • mikeTheLiar

    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;
    0
  • error
    Considered Harmful

    Is the WTF that they should have used 

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

    0
  • anotherusername

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

    0
  • mikeTheLiar

    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

    0
  • error
    Considered Harmful

    @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.
    0
  • R

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

    0
  • boomzilla
    ♿ (Parody)

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

    0
  • dhromed

    @boomzilla said:

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

    Ridiculous! I demand equal rights for AI!

    0
  • mikeTheLiar

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

    0
  • mikeTheLiar

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

    0
  • K

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

    0
  • R

    @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

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

    0
  • R

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

    0
Log in to reply