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 overcome sql injection Expand / Collapse
Author
Message
Posted Monday, July 14, 2008 1:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, July 14, 2008 1:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 40,172, Visits: 36,560
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
Posted Monday, July 14, 2008 2:03 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, August 23, 2014 6:03 AM
Points: 351, Visits: 1,556
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
Post #533313
Posted Monday, July 14, 2008 6:39 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:36 PM
Points: 35,347, Visits: 31,885
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #533445
Posted Monday, July 14, 2008 6:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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
Posted Monday, July 14, 2008 6:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 40,172, Visits: 36,560
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
Posted Monday, July 14, 2008 7:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Monday, July 14, 2008 10:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:13 PM
Points: 6,466, Visits: 13,919
some brilliant links and advice there, way to go guys :D

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

"Ya can't make an omelette without breaking just a few eggs"
Post #533690
Posted Tuesday, July 15, 2008 12:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:55 AM
Points: 201, Visits: 404
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
Posted Tuesday, July 15, 2008 7:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:36 PM
Points: 35,347, Visits: 31,885
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #534336
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse