How should I pass a table name into a stored proc?

Question

I just ran into a strange thing...there is some code on our site that is taking a giant SQL statement, modifying it in code by doing some search and replace based on some user values, and then passing it on to SQL Server as a query.

I was thinking that this would be cleaner as a parameterized query to a stored proc, with the user values as the parameters, but when I looked more closely I see why they might be doing it...the table that they are selecting from is variably dependant on those user values.

For instance, in one case if the values were ("FOO", "BAR") the query would end up being something like "SELECT * FROM FOO_BAR"

Is there an easy and clear way to do this? Everything I'm trying seems inelegant.

EDIT: I could, of course, dynamically generate the sql in the stored proc, and exec that (bleh), but at that point I'm wondering if I've gained anything.

EDIT2: Refactoring the table names in some intelligent way, say having them all in one table with the different names as a new column would be a nice way to solve all of this, which several people have pointed out directly, or alluded to. Sadly, it is not an option in this case.

Answer 1

@RBarry Young You don't need to add the brackets to @ActualTableName in the query string because it is already included in the result from the query in the INFORMATION_SCHEMA.TABLES. Otherwise, there will be error(s) when executed.

CREATE PROC spCountAnyTableRows( @PassedTableName as NVarchar(255) ) AS -- Counts the number of rows from any non-system Table, SAFELY BEGIN DECLARE @ActualTableName AS NVarchar(255)

SELECT @ActualTableName = QUOTENAME( TABLE_NAME )
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @PassedTableName

DECLARE @sql AS NVARCHAR(MAX)
--SELECT @sql = 'SELECT COUNT(*) FROM [' + @ActualTableName + '];'

-- changed to this
SELECT @sql = 'SELECT COUNT(*) FROM ' + @ActualTableName + ';'

EXEC(@SQL)

END

Answer 2

First of all, you should NEVER do SQL command compositions on a client app like this, that's what SQL Injection is. (Its OK for an admin tool that has no privs of its own, but not for a shared use application).

Secondly, yes, a parametrized call to a Stored procedure is both cleaner and safer.

However, as you will need to use Dynamic SQL to do this, you still do not want to include the passed string in the text of the executed query. Instead, you want to used the passed string to look up the names of the actual tables that the user should be allowed to query in the way.

Here's a simple naive example:

CREATE PROC spCountAnyTableRows( @PassedTableName as NVarchar(255) ) AS
-- Counts the number of rows from any non-system Table, *SAFELY*
BEGIN
    DECLARE @ActualTableName AS NVarchar(255)

    SELECT @ActualTableName = QUOTENAME( TABLE_NAME )
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = @PassedTableName

    DECLARE @sql AS NVARCHAR(MAX)
    SELECT @sql = 'SELECT COUNT(*) FROM ' + @ActualTableName + ';'

    EXEC(@SQL)
END

---

Some have fairly asked why this is safer. Hopefully, little Bobby Tables can make this clearer:

---

Answers to more questions:

1. QUOTENAME alone is not guaranteed to be safe. MS encourages us to use it, but they have not given a guarantee that it cannot be out-foxed by hackers. FYI, real Security is all about the guarantees. The table lookup with QUOTENAME, is another story, it's unbreakable.

2. QUOTENAME is not strictly necessary for this example, the Lookup translation on INFORMATION_SCHEMA alone is normally sufficient. QUOTENAME is in here because it is good form in security to include a complete and correct solution. QUOTENAME in here is actually protecting against a distinct, but similar potential problem know as latent injection.

Answer 3

Instead of Querying the tables based on user input values, you can pick the procedure instead. that is to say
1. Create a procedure FOO_BAR_prc and inside that you put the query 'select * from foo_bar' , that way the query will be precompiled by the database.
2. Then based on the user input now execute the correct procedure from your application code.

Since you have around 50 tables, this might not be a feasible solution though as it would require lot of work on your part.

Answer 4

Sounds like you'd be better off with an ORM solution.

I cringe when I see dynamic sql in a stored procedure.