May 4, 2017 at 6:45 pm
I'm about to build aselect statement as a string and execute it so I can create the column namesfrom text and a query. the field name will be val_alpha and I need to append a number dynamically to that for a complete column name.
Is there a way I cando this directly in a select statement? this is what I've started with as an example:
declare @a asvarchar(5)
declare @sel asvarchar(500)
declare @frm asvarchar(500)
set @a = '1'
set @sel = 'SELECTA.REG_NAME,val_alpha' + @a
set @frm = 'FROMdbo.F1ASR_REG_ASSET_ATTR AS A
wherea.attr_unique_id =''BE2BDE358FB348E79167972CE418A5935DB2F8F1'''
set @sel = @sel + '' + @frm
exec (@sel)
or can I somehow use
Select a.reg_name, 'val_alpha' + '1' from...
Bruce
May 5, 2017 at 2:09 am
To do this, you would need to use dynamic SQL, such as:DECLARE @Field_suffix char(1);
DECLARE @SQL varchar(MAX);
SET @Field_suffix = '1';
SET @SQL = 'SELECT A.REG_NAME,val_alpha' + @Field_suffix + '
FROM blah blah blah...';
--Printing as this isn't a full SQL statement in the example
PRINT @SQL;
--EXEC(@SQL);
GO
Note, however, that you need to heavily consider SQL injection when using methods like this. In my example I've used a char(1), which would make it difficult for someone to inject something, however, in your statement you have varchar(500); if this is a parametrised stored procedure you need to ensure that someone couldn't abuse it.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 5, 2017 at 2:52 am
If your worried about SQL Injection, you could have a look at Gail's "catch all" query and how she gets around SQL injection.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
While I appreciate this isn't a catch all scenario, the concept will still be the same of executing a built up dynamic string using parameters negating any injection foot print.
May 5, 2017 at 3:26 am
anthony.green - Friday, May 5, 2017 2:52 AMIf your worried about SQL Injection, you could have a look at Gail's "catch all" query and how she gets around SQL injection.http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
While I appreciate this isn't a catch all scenario, the concept will still be the same of executing a built up dynamic string using parameters negating any injection foot print.
Except that column names can't be parameterised, and hence that method of preventing injection can't be used.
The only semi-secure way to do this is to check, before executing, that 'val_alpha' + @a matches a legit column name in sys.columns, and refuse to execute if that's not the case.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 5, 2017 at 4:12 am
GilaMonster - Friday, May 5, 2017 3:26 AMExcept that column names can't be parameterised, and hence that method of preventing injection can't be used.The only semi-secure way to do this is to check, before executing, that 'val_alpha' + @a matches a legit column name in sys.columns, and refuse to execute if that's not the case.
My bad, thanks for the correction.
May 5, 2017 at 5:51 am
Thanks. I was hoping that by now they would have something and I would not have to setup a sp.
I've never heard of injection so thanks for that.
Bruce
May 5, 2017 at 6:14 am
You also want to use QUOTENAME() to handle complex names for your columns or objects.
DECLARE @Field_suffix char(1);
DECLARE @SQL varchar(MAX);
SET @Field_suffix = '1';
SELECT @SQL = 'SELECT A.REG_NAME,' + QUOTENAME( 'val_alpha' + @Field_suffix) + '
FROM blah blah blah...'
FROM sys.columns
WHERE name = 'val_alpha' + @Field_suffix
AND object_id = OBJECT_ID('YourTable');
--Printing as this isn't a full SQL statement in the example
PRINT @SQL;
--EXEC(@SQL);
GO
May 5, 2017 at 7:13 am
GilaMonster - Friday, May 5, 2017 3:26 AManthony.green - Friday, May 5, 2017 2:52 AMIf your worried about SQL Injection, you could have a look at Gail's "catch all" query and how she gets around SQL injection.http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
While I appreciate this isn't a catch all scenario, the concept will still be the same of executing a built up dynamic string using parameters negating any injection foot print.
Except that column names can't be parameterised, and hence that method of preventing injection can't be used.
The only semi-secure way to do this is to check, before executing, that 'val_alpha' + @a matches a legit column name in sys.columns, and refuse to execute if that's not the case.
+1,000,000 to that!
--Jeff Moden
May 7, 2017 at 5:42 pm
thanks, much appreciated.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply