SQL Server – pulling user data using dynamic query in stored procedure

Question

I have problem, I'm having trouble viewing user information using stored procedure. The procedure accepts three parameters: table, column and searchBySomething. Every time I want to search for a user using another column, the column variable receives the column of the id and the searchBySomething variable receives specific id, the procedure is work but when I'm send another column I'm get the error message

Invalid column name (the data)

The procedure looks like this :

ALTER PROCEDURE [dbo].[userDetailsDisplay] 
    @table NVARCHAR(30),
    @column NVARCHAR(30),
    @searchBySomething NVARCHAR(30)

    DECLARE @sql NVARCHAR(100)

    SET @sql = 'SELECT * FROM ' + @table + ' WHERE ' + @column + ' = ' + @searchBySomething 
    EXECUTE sp_executesql @sql

The error is pretty clear - the column you're passing in to search in doesn't exist in the table you're trying to search. What are the exact values that you're passing in for the parameters? — squillman, Oct 12 '18 at 16:23
Also, if your passed in column name has a space in it (or some other values, such as a hyphen) you need to delimit the column name. Use the QUOTENAME() function for your table and column names (which also improves security on this), and at the very minimum replace any single quote in @searchBySomething with two single quotes, e.g. `REPLACE(@searchBySomething, '''', '''''')` to help prevent simple injection attacks. — Laughing Vergil, Oct 12 '18 at 16:54
This code is terrible on all kinds of levels. Read Xedni's answer and embrace it. — Zohar Peled, Oct 12 '18 at 22:08
@squillman If the actual column name is `the data`, as shown in the error message, then the problem isn't that the column doesn't exist. The problem is trying to parse `... WHERE the column = ...`. As Laughing Virgil pointed out, `QuoteName` should at least allow a little more success in the parsing phase. (And a special shout-out to [Little Bobby Tables](http://bobby-tables.com/)!) — HABO, Oct 13 '18 at 16:00

score 3 · Answer 1 · answered Oct 12 '18 at 16:42

So the specific error you're getting is because you're not checking the input to see if the string being passed into @column actually exists. You can check for it's existence against the metadata catalog view sys.columns doing something like this:

if not exists
(
    select 1
    from sys.columns
    where object_id = object_id(@table)
        and name = @column
)
begin
    raiserror('Column %s does not exist in table %t', 16, 1, @column, @table)
    return
end

However I would be remiss if I didn't point out two things.

First, this dynamic table dynamic where clause pattern is very bad practice. If it's for someone who already has database access, they can simply query the tables themselves. And if it's for an external user, well, you've basically given them full database read access through this procedure. Of course there are some rare occasions where this pattern is needed, so if you're dead set on using dynamic sql, that leads me to my next point.

The code you've written is vulnerable to a SQL injection attack. Any time you use dynamic SQL you must be VERY careful how it's constructed. Say I passed in the column name ; drop database [admin]-- Assuming you had such a database, my could would happily be executed and your database would disappear.

How to make dynamic SQL safe is a complex topic, but if you're serious about learning more about it, this is probably one of the best articles you can find. http://www.sommarskog.se/dynamic_sql.html

By parameterizing your query and using quotename() on the table and column, I modified it to look like this. This will still throw weird errors if someone tries to do an injection attack, but at least it wont actually execute their code.

create procedure [dbo].[userDetailsDisplay] 
    @table nvarchar(30),
    @column nvarchar(30),
    @searchBySomething nvarchar(30)
as
begin
    declare 
        @sql nvarchar(max),
        @params nvarchar(1000)

    if not exists
    (
        select 1
        from sys.columns
        where object_id = object_id(@table)
            and name = @column
    )
    begin
        raiserror('Column %s does not exist in table %t', 16, 1, @column, @table)
        return
    end

    select @sql = '
        select * 
        from ' + quotename(@table) + ' WHERE ' + quotename(@column) + ' = @searchBySomething'

    execute sp_executesql 
        @stmt = @sql,
        @params = '@searchBySomething nvarchar(30)',
        @searchBySomething = @searchBySomething
end

That's because I'm explicitly throwing one. What would you like it to do if you pass an invalid column if not error? IMHO, the error is legitimate, this just illustrates why, and how you can check for it to behave differently should you so choose. — Xedni, Oct 12 '18 at 17:51
Just a side note to an otherwise very good answer - and I know this comes from the OP's original code - `@table` and `@column` should be of type [`sysname`](https://stackoverflow.com/questions/5720212/what-is-sysname-data-type-in-sql-server), not `nvarchar(30)`. — Zohar Peled, Oct 13 '18 at 06:25

score 0 · Answer 2 · answered Oct 12 '18 at 16:29

0

Just check to make sure that the column exist in the table.

for each @table called, check that the @column variable is in that table.

answered Oct 12 '18 at 16:29

MEdwin

2,940
1
14
27

score 0 · Answer 3 · edited Oct 12 '18 at 16:33

0

SET @sql = 'SELECT * FROM ' + @table + ' WHERE ' + @column + ' = ' +''' @searchBySomething +''''

Ex : select * from table where column ='value'

edited Oct 12 '18 at 16:33

mxmissile

11,464
3
53
79

answered Oct 12 '18 at 16:32

Ramapriyan C

65
1
8

SQL Server – pulling user data using dynamic query in stored procedure

3 Answers3