Post reply

COMPLEX QUERY --select a coloumn name as value and using the value in a SELECT query.

  • July 3, 2011 at 7:13 am

    #144125

    Hi All,

    Using the TABLE_NAME OR COLOUMN NAME from the result of a query and Using THESE values in a SELECT query getting the value in the COLOUMN.

    I got a scenario where i get the value(COLOUMN_NAME) from a query and using this value(COLOUMN_NAME) in a select query i need to get the value present in the COLOUMN_NAME.

    In the same way above,i have to get TABLE_NAME and use this to the above query.

    Finally the structure shd be like this

    COLOUMN_NAME=(select value1 from A)

    TABLE_NAME=(select value2 from A)

    select COLOUMN_NAME from TABLE_NAME

    or

    select (select value1 from A) from (select value2 from A)

    but sql server shows errors.

    Can Anyone help me out. Thanks in Advance.

  • July 3, 2011 at 7:41 am

    #1347537

    Look up sys.all_columns and sys.all_objects in Books On Line ... you will find your answer there. But may I ask, why do you need to do this?

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • July 4, 2011 at 8:47 am

    #1347899

    Here is a bit of dynamic SQL that should achieve the results you are looking for. I used your own suggested subquery in the places to populate the values of the @ColumnName and @TableName variables.

    declare @SQL as varchar(100), @ColumnName as varchar(100), @TableName as varchar(100);

    set @ColumnName = (select Value1 from A);

    set @TableName = (select Value2 from A);

    set @SQL = (select 'select ' + c.name + ' from ' + s.name + '.' + o.name from sys.all_columns as c inner join sys.all_objects as o on c.object_id = o.object_id inner join sys.schemas as s on o.schema_id = s.schema_id where c.name = @ColumnName and o.name = @TableName);

    print @SQL;

    exec (@SQL);

    Hope this helps!

  • July 5, 2011 at 8:49 am

    #1348327

    Hi,

    Thank you very much for the code you suggested,i was searching for a solution since some time.

    I used it by some improvising the code and it is working fine. 🙂

    Thanks,

    Hemanth

  • July 5, 2011 at 8:53 am

    #1348330

    Hi Ron,

    Thank you for your valuable suggestion.I learnt many technical issues.I got the solution from other forum member and it is working fine.:-)

    Thanks,

    Hemanth.

  • July 6, 2011 at 5:59 am

    #1348857

    I've got to ask, where are the values for the column and table name coming from?

    If they are entered by a user then you have opened yourself up to an SQL injection attack.

    See here

    Nigel
    How to post questions

  • July 6, 2011 at 6:58 am

    #1348897

    Nigel,

    I am unsure how this query, despite the fact that it generates a dynamic SQL statement, is vulnerable to a SQL injection attack. The values in the dynamically generated statement are pulled directly from the system tables and are not directly provided from the user input. If the values provided are not legitimate column and table values, would that not simply make the dynamic statement become a null value? Is that enough to qualify as an "attack"?

    I would entirely agree to a vulnerability if the dynamic SQL had been something like: 'select ' + @ColumnName + ' from ' + @TableName, but the extra layer of validation by looking up the values in the system tables is meant to preclude malicious variable values from becoming a factor.

    Having said all that, I am certainly open to the possibility that I am missing something that could cause an attack.

  • July 6, 2011 at 8:37 am

    #1349028

    Geoff,

    My bad. I hadn't noticed the use of system tables in your query (didn't scroll right far enough). 😀

    Nigel
    How to post questions

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply