how to overcome sql injection

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » how to overcome sql injection

15 posts, Page 1 of 2

1 2 »»

how to overcome sql injection

Rate Topic

Display Mode

Topic Options

Author

Message

satishkatarapu

Posted Monday, July 14, 2008 1:15 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, August 19, 2008 5:17 AM
Points: 5, Visits: 13

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.

Post #533298

GilaMonster

Posted Monday, July 14, 2008 1:21 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:37 PM
Points: 37,687, Visits: 29,944

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 2008, MVP
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

Post #533302

John.Sansom

Posted Monday, July 14, 2008 2:03 AM

Old Hand

Group: General Forum Members
Last Login: Friday, May 10, 2013 1:52 AM
Points: 343, Visits: 1,453

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.sqlbrit.com
The SQLBrit Community Forum - "There's so more to being a Data Professional than just technology."

Post #533313

Jeff Moden

Posted Monday, July 14, 2008 6:39 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 32,906, Visits: 26,789

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #533445

Hari.Sharma

Posted Monday, July 14, 2008 6:48 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 12:43 PM
Points: 1,330, Visits: 455

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

Cheers,
Hari
Tips & Tricks for SQL BI Developers

Post #533454

GilaMonster

Posted Monday, July 14, 2008 6:54 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:37 PM
Points: 37,687, Visits: 29,944

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 2008, MVP
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

Post #533459

Andrew Gothard-467944

Posted Monday, July 14, 2008 7:18 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 29, 2010 5:23 AM
Points: 194, Visits: 2,357

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

Post #533471

Perry Whittle

Posted Monday, July 14, 2008 10:01 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 5,201, Visits: 11,153

some brilliant links and advice there, way to go guys :D

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

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

Post #533690

SQLNightOwl

Posted Tuesday, July 15, 2008 12:16 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 22, 2013 3:55 PM
Points: 179, Visits: 391

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

Post #534063

Jeff Moden

Posted Tuesday, July 15, 2008 7:01 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 32,906, Visits: 26,789

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

Post #534336

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions