Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to prevent SQL Injection Attack from SQL server side


How to prevent SQL Injection Attack from SQL server side

Author
Message
DKG-967908
DKG-967908
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 239
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.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16564 Visits: 17015
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
aaron.reese
aaron.reese
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 898
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.
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5697 Visits: 7660
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.


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
DKG-967908
DKG-967908
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 239
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,
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5697 Visits: 7660
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.


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
aaron.reese
aaron.reese
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 898
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.
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5697 Visits: 7660
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.


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Markus
Markus
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1582 Visits: 3672
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.



Steve-3_5_7_9
Steve-3_5_7_9
SSC Eights!
SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)

Group: General Forum Members
Points: 982 Visits: 1583
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search