January 10, 2014 at 10:31 am
Hi all,
Is it possible to ban/prohibit all UNION queries at the Server or Database level?
The UNION clause is one of the most common ways that hackers use to append malicious SQL -- to add another layer of defence, I'd like to ban all UNION queries.
Regards
January 10, 2014 at 10:37 am
i'd like to see where you got a statistic or article stating UNION was a vector for hackers; might be interesting reading.
I'm more under the impression that SQL injection is the most pertinent vector to watch for, and after that it's higher level things like forcing buffer overruns to run malicious code, which would not be using a union query, but code to add sysadmins and stuff like that.
AFAIK, you could audit and report, but not prevent/rollback commands at the server level.
on specific tables, you can do it with a trigger;
i've got an example of a trigger on a table forcing a WHERE clause form a similar question here:
[
Lowell
January 10, 2014 at 10:38 am
Trying to figure out how a UNION (or UNION ALL) could be used to append malicious code to a SQL query. Can you provide a couple of examples?
January 10, 2014 at 11:48 am
Meltdown (1/10/2014)
Hi all,Is it possible to ban/prohibit all UNION queries at the Server or Database level?
The UNION clause is one of the most common ways that hackers use to append malicious SQL -- to add another layer of defence, I'd like to ban all UNION queries.
Regards
I'd recommend that you instead ban sql injection. That has the added benefit of banning UNION queries from the hackers while still allowing them at the server or database level.
January 10, 2014 at 12:25 pm
Lynn Pettis (1/10/2014)
Trying to figure out how a UNION (or UNION ALL) could be used to append malicious code to a SQL query. Can you provide a couple of examples?
It's one of the ways SQL injection can be used. Not the only one by far. It's the easiest way, if there is some code that is vulnerable to SQL injection to figure out the structure of the database.
Rather than trying to ban one of the many options that a hacker has if he finds a part of your app that's vulnerable to SQL injection, make sure there are no portions that are vulnerable to SQL injection
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 10, 2014 at 12:27 pm
Here's a few sql-injection examples being fired at the database - first three using UNION ALL.
display.aspx?f=32&status=\%27+and+999999.9+%0buNiOn%0baLl+%0bsElEcT+0x393133353134353632312e39,0x393133353134353632322e39,0x393133353134353632332e39,0x393133353134353632342e39,0x393133353134353632352e39,0x393133353134353632362e39,0x393133353134353632372e39,0x393133353134353632382e39,0x393133353134353632392e39,0x39313335313435363231302e39,0x39313335313435363231312e39,0x39313335313435363231322e39,0x39313335313435363231332e39,0x39313335313435363231342e39+and+\%271\%27=\%271®ion=&countyselection=&id=&page=4&order=desc&daysprune=-1
display.aspx?id=2&countyselection=Dublin999999.9\%27+%2f**%2fuNiOn%2f**%2faLl+%2f**%2fsElEcT+0x393133353134353632312e39,0x393133353134353632322e39,0x393133353134353632332e39,0x393133353134353632342e39,0x393133353134353632352e39,0x393133353134353632362e39,0x393133353134353632372e39,0x393133353134353632382e39+and+\%270\%27=\%270&status=1&forumid=3
AND t.county = 'Dublin999999.9' /**/uNiOn/**/aLl /**/sElEcT 0x393133353134353632312e39,0x393133353134353632322e39,0x393133353134353632332e39,0x393133353134353632342e39,0x393133353134353632352e39,0x393133353134353632362e39,0x393133353134353632372e39,0x393133353134353632382e39,0x393133353134353632392e39 and '0'='0';
and a CONCAT -
AND t.county = 'Dublin'(/**/sElEcT 1 /**/fRoM(/**/sElEcT count(*),/**/cOnCaT((/**/sElEcT(/**/sElEcT /**/uNhEx(/**/hEx(/**/cOnCaT(0x217e21,0x4142433134355a5136324457514146504f4959434644,0x217e21)))) /**/fRoM information_schema./**/tAbLeS /**/lImIt 0,1),floor(rand(0)*2))x /**/fRoM information_schema./**/tAbLeS /**/gRoUp/**/bY x)a) '';
January 10, 2014 at 12:30 pm
The above example is exactly why you should NEVER NEVER NEVER run pass through sql statements that have information posted from a public facing web page. Properly parameterize your queries and this is a non-issue.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 10, 2014 at 12:36 pm
Meltdown (1/10/2014)
Here's a few sql-injection examples being fired at the database - first three using UNION ALL.
The vulnerability there is not the union, it's the SQL injection. Prohibit union and I'll use IF with a waitfor, or check the nice descriptive error messages that are probably still turned on, or use INTERSECT in a creative way, or EXISTS, or any one of a pile of other options to work out the schema, then updates or inserts to put in values I want displayed.
You're suggesting putting a door up and ignoring that there's no wall in the first place.
Oh, and for the love of all that's holy, use POST, not GET for your web pages.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 10, 2014 at 12:38 pm
You can tell by the MiXeD cAsE that these hackers are especially elite! Google 'sql injection' and put a halt to it!
January 10, 2014 at 12:47 pm
Hi Sean,
Parameters are being used, I believe the database is safe and secure -- still don't like seeing the database being hammered with those types of queries.
I was just curious if there was a ON/OFF switch for UNION queries.
Regards
January 10, 2014 at 1:04 pm
Meltdown (1/10/2014)
I was just curious if there was a ON/OFF switch for UNION queries.
No.
But again, the problem is not union. The problem is SQL injection. If the code is not vulnerable to SQL Injection, then the UNION is never showing up as an executable part of the query and so banning union (even if possible) would achieve nothing. If the UNION is showing up as part of executing SQL queries, then your code is vulnerable to SQL Injection and banning a single aspect of it is like crossing an open doorway with a couple of pieces of sting and expecting it to keep intruders out.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 10, 2014 at 1:04 pm
Meltdown (1/10/2014)
Hi Sean,Parameters are being used, I believe the database is safe and secure -- still don't like seeing the database being hammered with those types of queries.
I was just curious if there was a ON/OFF switch for UNION queries.
Regards
Once you expose a webserver to the outside world you are going to hit with this periodically. It is the nature of the beast.
No you can't disable a UNION query nor should you. If as you say you have parameterized queries then the query isn't being run against your database. It will just be a nonsense value in the where clause.
Something like this as an example.
select *
from YourTable
where SomeColumn = 'select * from tablea union all select * from tableb'
It is not consuming any more resources or doing anything but whatever query you have defined. What you are seeing is an attempt by some "leet" kid who thinks he is clever. It is good to be concerned about this stuff and even better to investigate when you see these things happening.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 10, 2014 at 1:04 pm
The database most definitely isn't safe and secure if queries like those are being injected in. As Gail and the others have said, your code's open for injection, and, if the account that accesses your data via your interface has the right privileges, whoever's injecting your page has more than likely lifted some data from the system, if that last query is any indication of where they're digging.
Barring UNION won't stop this in the slightest, as the last query shows; they can just plug in another operator or just run a different query structure in general and still get what they want. The answer is to stop the problem at the source and proof your code against injection, as the others have already stated.
However, is this part of a vendor-based application? If so, I know that can be an entirely different beast. The business I work with uses an application by requirement of a government agency, and that application is vulnerable to SQL injection. Ironically, this makes the application's use a violation of government security policies, and yet, we can't convince the agency to use a different application, sensitive information being subject to theft or not. Fun!
In any case, if you have the ability to patch this up, doing so is a definite must. If it's a vendor issue, contact them, show them proof of the injection (and self-inject the application with something safe, if it's hosted on you/your company's hardware, to fully demonstrate that, yes, this thing is vulnerable), and ask that the vulnerability be plugged. Vulnerable code is a gateway to untold disaster that WILL happen sooner or later.
- 😀
January 10, 2014 at 1:05 pm
I'm trying to figure out why you would want to restrict your developers from using a valid construct for solving user requests.
January 10, 2014 at 1:08 pm
hisakimatama (1/10/2014)
The database most definitely isn't safe and secure if queries like those are being injected in. As Gail and the others have said, your code's open for injection, and, if the account that accesses your data via your interface has the right privileges, whoever's injecting your page has more than likely lifted some data from the system, if that last query is any indication of where they're digging.
Whoa!!! We don't know that the code is vulnerable. We have not seen a single line of t-sql posted. All we have seen so far is a url with some query string parameters that appear to look like somebody attempting sql injection. The OP stated that all of their queries are parameterized (procs would be even better) which means the injection is not happening. It is not uncommon to log the entire URL for page loads, especially when the page throws an exception.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply