How to protect queries with parameterised table names from injection

Question

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?

Yip - don't use dynamic SQL - I know your pain not wanting to write a SP for each table that needs an update, but thats the proper/correct/safe way to do it. — Dale K
@DaleBurrell so would you suggest a client side IF DbName = School1 THEN <run SP for School1 DB> ... type thing? Or could I put conditional statements into the procedure to run a second one with a pre-defined database? — Isaac Reefman
Well I have no knowledge of your client side, but usually people use some form of domain modelling in which each class (which often is closely linked to a database record) is handled by different code. These days many people use an ORM. Even hand-coding it, which I have code to maintain, the code for each class is separate and saves to its own database table. — Dale K
I'm starting to lean towards the second idea in my comment though - building a test environment to try passing DBName and data variables to a SP that checks the DBName against a set of options, then passes the remaining variables to the appropriate second SP... — Isaac Reefman

Isaac Reefman Isaac Reefman · Accepted Answer · 2019-05-23T08:13:40

Dale mentioned that I'd have to have a separate Stored Procedure for each database, which gave me the idea of splitting it into two separate ones; one that only works out which database to go for and then passes the values to the other one, which actually adds the student record. It's not especially elegant, but it lets you do the lot in SQL:

First, the query that adds a student, exactly as you'd expect - put this in each of the databases:

CREATE Procedure [dbo].[AddStudent] (@FirstName char(10), @LastName char(10)) AS
    INSERT INTO SCHOOL1.dbo.Student (FirstName, LastName) VALUES
        (@FirstName, @LastName);

Then another one, which can live in a master database, or alternatively in each of the databases, depending on what's most convenient to send it to:

CREATE Procedure [dbo].[AddStudentUniversal] (@DbName char(10), @FirstName char(10), @LastName char(10)) AS
    IF (@DbName = 'SCHOOL1')  EXECUTE SCHOOL1.dbo.AddStudent @FirstName, @LastName
    IF (@DbName = 'SCHOOL2')  EXECUTE SCHOOL2.dbo.AddStudent @FirstName, @LastName

How to protect queries with parameterised table names from injection

1 Answers