Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 525
  • Last Modified:

SQL Server "QUOTENAME('nu.myColumn')

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?
0
Phil5780
Asked:
Phil5780
  • 2
  • 2
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Can you post your current query so that alternatives can be found out..
0
 
Phil5780Author Commented:
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?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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..
0
 
dbidbaCommented:
Rather than using quotename(), do the quoting manually.

i.e.
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.


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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now