June 20, 2013 at 12:05 pm
Hi Everyone,
Is it possible to stop SQL Injection Attack at SQL server level?
I have gone through some posts and articles that suggest all the checks at application level so that only authentic data can be entered into database.
My client has a travel portal and facing SQL injection attack. My knowledge is limited in this topic. Please can anyone help and let me know in case we can do something at SQL server level so that it can be stopped.
An early response would be highly appreciated.
thanks in advance.
June 20, 2013 at 12:15 pm
DKG-967908 (6/20/2013)
Hi Everyone,Is it possible to stop SQL Injection Attack at SQL server level?
I have gone through some posts and articles that suggest all the checks at application level so that only authentic data can be entered into database.
My client has a travel portal and facing SQL injection attack. My knowledge is limited in this topic. Please can anyone help and let me know in case we can do something at SQL server level so that it can be stopped.
An early response would be highly appreciated.
thanks in advance.
My guess is the application is creating a sql string and then executing it? That is the classic sql injection vulnerability. The bet solution is either create stored procedures for everything and/or make calls to sp_executesql and use parameters. Somehow you must start using parameterized sql or the injection vulnerability will remain.
_______________________________________________________________
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/
June 20, 2013 at 2:34 pm
Plus 1 for Shauns suggestion.
Any interfacing with the database should be done through stored procedures. Not only does this prevent injection attacks, it will also improve develoment because the business logic can be built into the database where it can be shared between different front ends. Search the web for n-tier architecture. Some people prefer a business application layer so that in theory both the front end and database can be swapped out.
June 20, 2013 at 3:42 pm
SQL Injection works because it's sending down direct SQL commands against the interpreter. Because of the nature of it, SQL can't block it because it's meant to send legitimate batches to the compiler.
SQL cannot protect itself from legitimate commands.
This is one of the reasons that SQL DBA's and Devs alike insist on Stored Procedures to be used for external access to the database. By restricting a login to only execute rights on procedures, you've limited the damage that login can do. For some cases like user defined search screens data_reader can be allowed for sp_executeSQL calls with parameters.
The front end must protect the database from injection if you're not letting the database protect itself by disallowing the process in the first place.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 20, 2013 at 6:15 pm
Thanks all for your valuable inputs.
As per "The front end must protect the database from injection if you're not letting the database protect itself by disallowing the process in the first place."
this is again something which needs to be protected from front end - If I have understood correctly - data should be filtered and validated from the found end application so that it can protect the database from injection.
But for this application needs to be validated in the first instance? am i right?
Being a DB Admin can we do something from the SQL server end? if so please can you point out all the suggestions.
thanks,
June 21, 2013 at 11:18 am
DKG-967908 (6/20/2013)
Being a DB Admin can we do something from the SQL server end? if so please can you point out all the suggestions.
Don't allow ad-hoc querying and restrict the application logins to execute proc rights only.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 21, 2013 at 12:53 pm
at the very least the front end app should be escaping strings. A typical attack occurs where you are running a script like
$SQL = "SELECT * FROM CUSTOMER WHERE NAME = '" + $NAME +"'"
if $NAME is "Bob" then the query ends up as
SELECT * FROM CUSTOMER WHERE NAME = 'Bob'
Which is a perfectly good SQL statement.
If the user sets $NAME = "';DELETE * FROM CUSTOMER;SELECT '"
$SQL will now look like
SELECT * FROM CUSTOMER WHERE NAME = '';DELETE * FROM CUSTOMER;SELECT ''
Which is also a perfectly good set of SQL commands and hey presto the attacker has just deleted all of your customers.
If you escape the control characters and convert the ' into & quot; the worst you will get is a badly formed query.
In fact this post is a really good example. I have to put a space between the ampersand and the quote otherwise you will just see the quote marks. When the text of the post is stored in the db is is escaped to the ampersand escape sequence and then converted back when the data is shown on the screen. This protects the db and application from an injection attack.
June 25, 2013 at 3:44 pm
Aaron,
Just in case you're not aware, best practice when allowing adhoc queries is to enforce usage of sp_executeSQL, because you can parameterize it and avoid injection attacks that way without a lot of overhead headache with the front end having to clean the parameters.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 26, 2013 at 5:48 am
One other way to help minimize an attack is have the userid ONLY have access to what it needs to do. For instance, don't assign it 'sa' rights or 'ddladmin' rights.... if so an attack could do a lot of harm.
June 26, 2013 at 7:55 am
Preventing SQL Injection is a multi-tiered approach.
1. The application needs to inspect the strings where there was input from the user
Here is an MSDN article which lists characters which should be removed from the strings:
http://msdn.microsoft.com/en-us/library/ms161953(v=sql.105).aspx
2. On the SQL Server, you would
a. as stated before, only give minimal permissions for the application
b. use stored procedures or parameterized queries
None of these are quick changes, other than perhaps restricting the permissions that the application has and testing to see if it breaks anythign.
SQL Injection is one of those things that need to be considered at the very beginning of application and database design, not an after thought. The DBA should make and enforce standards for accessing the database.
article:
http://msdn.microsoft.com/en-us/magazine/cc163917.aspx
Steve
June 27, 2013 at 10:30 am
Even trying to parse the incoming string for DELETE, DROP TABLE, etc. is doomed to fail.
A sneakier attack uses HEX, such as 0x77616974666f722064656c61792027303a303a323027
What does that unreadable string mean ?
[font="Courier New"]DECLARE @x varchar(99)
SET @x=0x77616974666f722064656c61792027303a303a323027
SELECT @x
==> waitfor delay '0:0:20'[/font]
Waiting for 20 seconds is a standard trick for hackers to check if an application can transmit commands to the database engine.
Always use parameters, not string concatenation.
June 27, 2013 at 11:56 am
I must say that I have never heard of a hex code attack; so, thank you for causing me to look into that.
My recommendation is layering. If you do an internet search on SQL Injection you will come up with all the same recommendations previously mentioned: use stored procs, tighten security with lowest level permissions, use sp_executesql with ad hoc query execution, and force developers to use parameterized queries.
But the supreme rule is: all input should be viewed as questionable whether it is coming from a source internal or external to your organization. You can build yourself some "cleansing" type functions to apply to string and binary type parameter inputs; but, know that they may need to be updated/tweaked from time to time as new threats come along and the line between legitimate and illegitimate input is blury some times.
The only way to be truly protected is to operate disconnected in a vacuum which is pointless; so, do the best that you can and be prepared for the worst--backup your databases and practice recovery from time to time.
June 27, 2013 at 12:35 pm
Ed Thompson (6/27/2013)
But the supreme rule is: all input should be viewed as questionable whether it is coming from a source internal or external to your organization. You can build yourself some "cleansing" type functions to apply to string and binary type parameter inputs; but, know that they may need to be updated/tweaked from time to time as new threats come along and the line between legitimate and illegitimate input is blury some times.
Well-stated, Ed. I view the first layer of defense as being to treat everything as suspect. Make sure the quotes are in order by building and using a standard library of functions to clean every string you pass to SQL.
June 27, 2013 at 12:38 pm
Humm....
How do you "clean" a hex input ? convert it to string and also check the resulting string ?
June 27, 2013 at 12:59 pm
Ed Wagner (6/27/2013)
Ed Thompson (6/27/2013)
But the supreme rule is: all input should be viewed as questionable whether it is coming from a source internal or external to your organization. You can build yourself some "cleansing" type functions to apply to string and binary type parameter inputs; but, know that they may need to be updated/tweaked from time to time as new threats come along and the line between legitimate and illegitimate input is blury some times.Well-stated, Ed. I view the first layer of defense as being to treat everything as suspect. Make sure the quotes are in order by building and using a standard library of functions to clean every string you pass to SQL.
I would disagree with this. Don't try to clean the input, protect yourself from malicious input by parameterizing your queries. DO NOT EVER execute user entered values. That means you do not create some code to build a sql string and then run that string against your database.
_______________________________________________________________
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 15 total)
You must be logged in to reply to this topic. Login to reply