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

by

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:
0
alt text

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.

I should note that you can do the same thing with dynamic Column Names and the INFORMATION_SCHEMA.COLUMNS table.

You can also bypass the need for stored procedures by using a parameterized SQL query instead (see here: https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.parameters?view=netframework-4.8). But I think that stored procedures provide a more manageable and less error-prone security facility for cases like this.

More Related Contents:

  1. How do I UPDATE from a SELECT in SQL Server?
  2. How to calculate age (in years) based on Date of Birth and getDate()
  3. CROSS JOIN vs INNER JOIN in SQL
  4. Convert Month Number to Month Name Function in SQL
  5. Function vs. Stored Procedure in SQL Server
  6. Multiple INSERT statements vs. single INSERT with multiple VALUES
  7. Avoid duplicates in INSERT INTO SELECT query in SQL Server
  8. Generate Dates between date ranges
  9. Select group of rows that match all items in a list
  10. SQL Server check case-sensitivity?
  11. How to print VARCHAR(MAX) using Print Statement?
  12. How to update Identity Column in SQL Server?
  13. Concatenate values based on ID
  14. Query error with ambiguous column name in SQL
  15. Why (and how) to split column using master..spt_values?
  16. Splitting delimited values in a SQL column into multiple rows
  17. What does =* mean?
  18. Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON? [duplicate]
  19. SQL Select Upcoming Birthdays
  20. How can I include null values in a MIN or MAX?
  21. Find the real column name of an alias used in a view?
  22. How to get the identity of an inserted row?
  23. Get the last day of the month in SQL
  24. TSQL – How to use GO inside of a BEGIN .. END block?
  25. SQL take just the numeric values from a varchar
  26. SQL Server FOR XML Path make repeating nodes
  27. WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT
  28. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  29. Date serial in SQL?
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment