Quick SQL Injection question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Quick SQL Injection question

Author

Topic

KrafDinner
Starting Member

34 Posts

Posted - 2010-10-29 : 11:04:13

I'm not exactly sure about this, and I figured the brains here could certainly straighten me out.

I'm going to have a stored procedure doing an insert... something like the following...



INSERT INTO Estimates (claimID, amount, provider, dateIn, notes, visible)
VALUES (@claim, @amount, @provider, @date, @notes, 1)

Now while I realize that in a normal situation this would be a terrible idea and leave one wide open for injection, what if the arguments are parameterized in the front end ? I'm writing a java application to add the estimates, and I'm wondering if using a PreparedStatement to do the insert would alleviate the possibility for injection.

For example, say I do the actual SQL in the Java front-end.. would that eliminate the possibility of injection or would it still leave me open to attacks ?

KrafDinner
Starting Member

34 Posts

Posted - 2010-10-29 : 11:43:45

Sorry to re-post on my own post, but after more research I have discovered that PreparedStatements do, in fact, protect from SQL Injection.

(I also realize this may not have been the best place for this question as it was more a java question. Thanks for your time all the same.)

Kristen
Test

22859 Posts

Posted - 2010-10-29 : 14:35:17

"leave one wide open for injection,"

No it won't

. This:


CREATE PROCEDURE(@claim int, ...)
AS
INSERT INTO Estimates (claimID, amount, provider, dateIn, notes, visible)
VALUES (@claim, @amount, @provider, @date, @notes, 1)

has no SQL Injection risk.

However, if you call the Sproc from your application with dynamic SQL then you have a potential problem at that point. But if you call the Sproc using parametrise-query then you will be fine (as you will with dynamic SQL if you make sure you double-up any embedded single quotes - on EVERY field in EVERY location in your application WITHOUT FAIL ... that tends to be the tricky bit as being human you might miss one!

I don't know what PreparedStatements do for you, in this regard, so can't confirm/deny that bit, sorry.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources