Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to prevent SQL Injection Attack from SQL server side Expand / Collapse
Author
Message
Posted Thursday, June 20, 2013 12:05 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 20, 2014 5:47 AM
Points: 62, Visits: 236
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.
Post #1465876
Posted Thursday, June 20, 2013 12:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 13,086, Visits: 12,553
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)
Post #1465884
Posted Thursday, June 20, 2013 2:34 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
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.
Post #1465941
Posted Thursday, June 20, 2013 3:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:08 PM
Points: 5,401, Visits: 7,514
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
Post #1465971
Posted Thursday, June 20, 2013 6:15 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 20, 2014 5:47 AM
Points: 62, Visits: 236
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,
Post #1466014
Posted Friday, June 21, 2013 11:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:08 PM
Points: 5,401, Visits: 7,514
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
Post #1466321
Posted Friday, June 21, 2013 12:53 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
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.
Post #1466354
Posted Tuesday, June 25, 2013 3:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:08 PM
Points: 5,401, Visits: 7,514
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
Post #1467398
Posted Wednesday, June 26, 2013 5:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 5:17 AM
Points: 1,289, Visits: 2,844
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.


Post #1467593
Posted Wednesday, June 26, 2013 7:55 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:22 AM
Points: 920, Visits: 1,437
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




Post #1467678
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse