SQL Server "QUOTENAME('nu.myColumn')

Posted on 2010-01-05
Last Modified: 2013-12-08
Hello All,
Now I believe I know the answer to this one be just wanted to make sure.  I'm contructing dynamic SQL using string concantenation with 'sp_executesql' and 'QUOTENAME()' for columns.  The query involved requires use of table aliases.  So when a column is dynamically passed into my proc it arrives as "nu.myColumn".  When QUOTENAME hits that value it raises an error because it wants to see just "myColumn".  Is there any decent, well formed, way around this?
Question by:Phil5780
    LVL 57

    Expert Comment

    by:Raja Jegan R
    Can you post your current query so that alternatives can be found out..

    Author Comment

    Sorry, I can't post this code, it's confidential.  I'm believe my query needs complete redesign to not require aliased dynamic table names (such as 'nu.testColumn').  A post rewrite dynamic table name would look like 'testColumn'.  Is this the right direction to ensure sql injection attacks?
    LVL 57

    Expert Comment

    by:Raja Jegan R
    >> Sorry, I can't post this code, it's confidential.

    I meant at least the code structure..
    Replace your column and table names to some dummy values like xxx or yyy like that..

    >> I'm believe my query needs complete redesign to not require aliased dynamic table names

    If possible give the modified version of your code so that I can help you with the redesign and optimal approach..
    LVL 5

    Accepted Solution

    Rather than using quotename(), do the quoting manually.

    select @Cmd = '''' + @SomeVariable + ''''

    Either way, be sure to check for nulls...
    select @Cmd = '''' + isnull(@SomeVariable,'') + ''''
    A null concatinated to your command somewhere can create an issue which is hard to debug. If you isnull() it to an empty string, at least your whole command doesn't turn into a null and you can find the missing piece of the command.


    Author Closing Comment

    My site has very high volume.  Is that more efficient than QUOTENAME()?

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Suggested Solutions

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
    This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now