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;
-
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
-
@mikeTheLiar said:
WTF! Obviously that needs to be base-64 encoded.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
-
@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%2c65At 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%2c65At 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%2c65At 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
-
@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.