Results 1 to 4 of 4

Thread: SQL variables

  1. #1
    Join Date
    Apr 2014
    Posts
    1

    Cool Unanswered: SQL variables

    I'm creating a sql template for users but I need the user to be able to change table names without doing a replace on a generic table name.

    This is not working:

    declare @client_schema varchar(50)
    set @client_schema = 'NewTbl'

    select * from +@client_schema.Registration

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You're creating a SQL Injection problem. It will be quicker and much less effort if you simply issue a DROP DATABASE command now to get rid of the database before you waste a lot of time creating tables, entering data, and later discovering that they've been corrupted.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    To illustrate Pat's point:
    Code:
    DECLARE @client_schema varchar(50);
    
    SET @client_schema = 'NewTbl.Registration; DROP DATABASE your_db;--';
    
    PRINT 'select * from ' + @client_schema + '.Registration';
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Actually, GVee, that will not work. The current SPID will have a lock on the database, and the database drop will spin, and eventually time out. The attacker would need to drop tables individually, or much worse, update them with random crud, so you can't tell the good data from the bad data for about a month or so after the attack.

    The general point is valid, though ;-)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •