how to overcome sql injection

Question

how to overcome sql injection

satishkatarapu

SSC Enthusiast

Points: 115
More actions
July 14, 2008 at 1:15 am

#190669

Hi
we are not to keep our database or tables secured inspite of having password changed ,firewalls and spyware live. the hacker is injecting the java script code in the records of the tables . we found that code insertion is done at where the user is giving the input data. how to avoid this problem.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Parameterise all queries in your front end app. Do not concatenated together SQL statements and execute them.

Preferably, use stored procedures for data access only and do not allow the application user to have any rights to the base tables. The application user should have only execute rights on the stored procedures.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John.Sansom SSCertifiable Points: 7335 More actions · Answer 2

Hi,

Here are some recommendations for you:

1. Secure the Web Tier

You need to ensure that your application/website tier validates all user input before passing it on to the database layer. Rather than checking for invalid characters (quotations etc.), as there are potentially many, I recommend to my clients that they define a list of “valid” input values for their interfaces/forms etc.

2. Use Bound Parameters

In order to negate SQL Injection you need to ensure that any parameters that are passed to SQL calls are adequately bound.

3. Use Different Connections

Use different connections/logins for different tasks. I.e. the connection that is validating a user’s email address does not need to have update permissions to the database.I often recommend to clients that they use a connection/account with minimal privileges for all operations (i.e. logging a user into their system) unless otherwise necessary. Once a user has been authenticated they can be provided with access to/via another connection that has more privileges.

4. Use Stored Procedures

Use stored procedures to interact with your database rather than generating/building SQL dynamically.

Also take a look at the following Microsoft article: http://msdn.microsoft.com/en-us/library/ms161953.aspx

Hope this helps.

John Sansom (@sqlBrit) | www.johnsansom.com

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

In other words, you will need to rewrite part of the app to use stored procedures instead of dynamic embedded SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 4

The best way to avoid SQL Injection is use of Stored Procedures.

Cheers,
Hari
Tips & Tricks for SQL BI Developers

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Hari.Sharma (7/14/2008)
The best way to avoid SQL Injection is use of Stored Procedures.

The only way to 100% for certain avoid SQL injection is to use properly parameterised queries or stored procedures.

And don't make the mistake of using stored procedures that dynamically build up SQL strings inside the proc and then exec it. Procs like that are just as vulnerable to SQL injection as dynamic string on the front end.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Andrew Gothard-467944 Hall of Fame Points: 3928 More actions · Answer 6

A decent overview with examples and links is in Earland Sommarskog's article here;

http://www.sommarskog.se/dynamic_sql.html#SQL_injection

There's a lot more out there covering in more detail, but IMO this is a good starting point for getting the basic idea

hth Andrew

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

some brilliant links and advice there, way to go guys 😀

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

SqlNightOwl SSCrazy Points: 2332 More actions · Answer 8

I'd say GilaMonster has it right. Dynamic SQL in any form (in an application or in a stored procedure) is vulnerable to SQL injection. The safest and best place for SQL DML code is in the database as a stored procedure. You could think of the stored procedures as the methods to a database object. In the .NET world you don't grant access to the internal code and data structures except thru properties and methods, so don't treat your database differently. Never grant users the ability to select, insert, update or delete anything directly from a table or view. Only allow them to execute stored procedures.

This is a point of control for the data. Besides, you don't want to face a news crew (or your boss) to try and explain how the data was compromised. Especially since these practices have been

best practices

for over a decade.

--Paul Hunter

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

paulhunter (7/15/2008)
In the .NET world you don't grant access to the internal code and data structures except thru properties and methods, so don't treat your database differently.

THAT is one of the best justifications for stored procedures that I've seen. Thanks, Paul.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 10

It's important to point out that stored procedures and parameterized queries aren't the full answer. The comment about dynamic SQL is key. If there's any place that queries are being built and executed, such as using EXEC() or sp_executesql in a stored procedure, you are still potentially vulnerable. The general recommendations:

1) Use stored procedures

2) Use parameters at the application layer

3) Do not use any dynamic SQL

4) Validate your input anyway

If you have any dynamic SQL, #4 is an absolute must. Even if you don't have dynamic SQL, you should still do #4. #1 and #2 should be non-negotiable for an application facing the Internet.

K. Brian Kelley
@kbriankelley

Shaun McGuile SSCarpal Tunnel Points: 4141 More actions · Answer 11

I agree with what everyone has said, paramterised stored procs etc.

Also I would remove all permissions from the public role as well, as these permissions override any login/user permissions in the database.

Also do you have the SQL scripts to clean your infected data?

--Shaun

Hiding under a desk from SSIS Implemenation Work :crazy:

Ron McCullough SSC Guru Points: 63877 More actions · Answer 12

Agree with all that has been said without exception.

Ran across this new Microsoft tool for use with an IIS server that could be of some use to you.

Microsoft has released a new security tool which Microsoft states:

"UrlScan version 3.0 Beta is a security tool that restricts the types of HTTP requests that Internet Information Services (IIS) will process. By blocking specific HTTP requests, UrlScan helps prevent potentially harmful requests from being processed by web applications on the server."

Download URL:

http://www.microsoft.com/downloads/details.aspx?FamilyID=ee41818f-3363-4e24-9940-321603531989&DisplayLang=en

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 13

This is a good article on how to avoid SQL Injection, even if you do not use stored procedures.

Always Use Parameters. Even if you don't use Stored Procedures.

http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 14

bitbucket (7/16/2008)
Agree with all that has been said without exception.
Ran across this new Microsoft tool for use with an IIS server that could be of some use to you.
Microsoft has released a new security tool which Microsoft states:
"UrlScan version 3.0 Beta is a security tool that restricts the types of HTTP requests that Internet Information Services (IIS) will process. By blocking specific HTTP requests, UrlScan helps prevent potentially harmful requests from being processed by web applications on the server."
Download URL:
http://www.microsoft.com/downloads/details.aspx?FamilyID=ee41818f-3363-4e24-9940-321603531989&DisplayLang=en

URLScan can block certain patterns, but for that sort of thing it can be problematic because it'll block the pattern, even if it's legit. So if you have some who enters -- and that's the pattern you are keying on, it's blocked.

K. Brian Kelley
@kbriankelley