Looking for a way to interact with data in different tables on the same server, I came across the solution offered on this question. As far as I've been able to find, there's no other way to 'pass' a table name to a query. Problem is it seems vulnerable to SQL Injection; for example, I could use this code to add someone to a 'Student' table in a database called 'School1':
CREATE PROCEDURE AddStudent (
@DBName char(10),
@FirstName char(30),
@LastName char(30)
) AS
DECLARE @SqlScript varchar(MAX) = '
INSERT INTO ' + @DBName + '.dbo.student (FirstName, LastName) VALUES (' +
@FirstName + ', ' + @LastName + ');'
EXECUTE (@SqlScript)
When I call the stored procedure 'AddStudent' I simply pass the variables 'School1', 'Bobby' and 'Tables'. You may see where this is going...
Even if the database name is somehow programatically (and therefore safely) determined client side, little Bobby Tables could choose to use his full name and ruin things with @FirstName
= Robert, @LastName
= '); DROP TABLE student;-- causing us all sorts of problems.
Sure, that may be an unlikely and extreme example, but you get the picture. Is there any way to prevent this?
No comments:
Post a Comment