SQL injection attacks

Question

SQL injection attacks

Jules Bonnot

SSCertifiable

Points: 5488
More actions
June 8, 2006 at 4:26 am

#172750

I am writing a stored procedure which accepts an account number, a username and a password. The account number directs the user to their specific database. The username and passord are then verified in that DB. This means i have to use dynamic SQL (i think). I am concerned about SQL injection attacks particularly as this in a login screen.
I found the article below but do not see how the solution offered would prevent SQL injections. Does it really work, why?
Does anyone know a better way to prevent SQL injection attacks in SPs which contain dynamic SQL.
Or is there a way i can access different DBs based on a paramter without using dynamic SQL?
http://www.sqlmag.com/articles/index.cfm?articleid=42216
Thanks,
Jules

Edit[ Have answered my own question. sp_executesql essentailly can take a parameterised query. so you can pass parameter as you you would to a a normal SP. this means you dont have to tag the string params onto your sql statement directly.
www.sql-library.com[/url]

Viewing 4 posts - 1 through 3 (of 3 total)

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

stax68 SSChampion Points: 11711 More actions · Answer 1

If were just a question of applying parameters, you wouldn't need the dynamic SQL. Presumably you want to issue a command which involves a DB in non-subsitutable position. Is the verification against the new DB done in T-SQL using tables, or is it a SQL login or a check against SQL user info? Do you then want the connection permanently diverted? If you are looking up a DB name from a switchboard table, the only thing a user can do is change the DBID they (effectively) enter. So if your dynamic SQL just uses a DB name from that table, there is no scope ofr SQL injection.

SQL injection only occurs where free text entered by the user is concatenated into a SQL string for dynamic execution.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 2

Yep the user name and password are checked in dynamic T-SQL this is because the DB they are in is passed in as a parameter.

But a structure like

CREATE PROCEDURE search_orders @custid nchar(5) = NULL,

@shipname nvarchar(40) = NULL AS

DECLARE @sql nvarchar(4000)

SELECT @sql = ' SELECT OrderID, OrderDate, CustomerID, ShipName ' +

' FROM dbo.Orders WHERE 1 = 1 '

IF @custid IS NOT NULL

SELECT @sql = @sql + ' AND CustomerID LIKE @custid

IF @shipname IS NOT NULL

SELECT @sql = @sql + ' AND ShipName LIKE @shipname

EXEC sp_executesql @sql, N'@custid nchar(5), @shipname nvarchar(40)',

@custid, @shipname

removes the threat of SQL injection

www.sql-library.com[/url]

stax68 SSChampion Points: 11711 More actions · Answer 3

Yes - SQL injection only occurs where free text entered by the user is concatenated into a SQL string for dynamic execution.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant