how to dyanamically alias a column?

[yehong]

Hi, Is it possible to create a dynamic alias name based upon a field value?
For exp:

Select id,
case when month=1 then cnt_of_tkts end as month
,case when month=2 then cnt_of_tkts end as month
,case when month=3 then cnt_of_tkts end as month
from
mytable;

Here month is a column name of a table and I would like its value (01,02,03) to be column headings of my query: Result

id 01 02 03
1001 15 10 11

Thanks for your help.

 

[chigley]

This could be done using dynamic SQL.

Declare a big enough VARCHAR, build your SQL in that and then execute it using EXEC

DECLARE @sql VARCHAR(2000)
DECLARE @id INT

SELECT @id = id FROM FOO
SET @SQL = 'SELECT '

IF @id =1 THEN
  SET @sql = @SQL + 'cnt_of_tkts 1'
IF @id =2 THEN
  SET @sql = @SQL + 'cnt_of_tkts 2'
IF @id =3 THEN
  SET @sql = @SQL + 'cnt_of_tkts 3'

SET @SQL = @SQL + ' FROM FOOBAR'

EXEC (@SQL)

It's not clear in your example where id comes from or how it relates to the data in your table so I have made some assumptions with the code above. Dynamic SQL does come with a performance hit as SQL Server has to compile the stored procedure each time.


Charlie Benger-Stevenson
Hart Hill IT Ltd

http://www.harthill-it.co.uk

 

[ca8msm]

Could you just hard-code the values as if it is for a month, you know how many there will be e.g.

Select id,
case when month=1 then cnt_of_tkts end as 01
,case when month=2 then cnt_of_tkts end as 02
,case when month=3 then cnt_of_tkts end as 03 etc...

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[SQLSister]

dynamic sql is a security risk as well as less efficient. ca8msm has the preferred solution.

"NOTHING is more important in a database than integrity." ESquared

 

[chigley]

How is dynamic sql a security risk if its in a stored procedure?

Charlie Benger-Stevenson
Hart Hill IT Ltd

http://www.harthill-it.co.uk

 

[ca8msm]

How is dynamic sql a security risk if its in a stored procedure?

Because it can still leave the database open to sql injection attacks.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[chigley]

How? Can you post an article link?
I understand that if you use dynamic sql, ie literal sql queries compiled from the gui level

"SELECT * FROM " + txtTableName.text;

then sql injection attacks are a worry. But if the dynamic sql was wrapped in a stored procedure, and the stored procedure was then executed using prepared statments I am sure this is ok.

Charlie Benger-Stevenson
Hart Hill IT Ltd

http://www.harthill-it.co.uk

 

[SQLSister]

and you would be wrong

http://www.sommarskog.se/dynamic_sql.html

Also you must set permission at the table level when using dynamic sql in a stored proc which is a further security problem.

"NOTHING is more important in a database than integrity." ESquared

 

[ca8msm]

But if the dynamic sql was wrapped in a stored procedure, and the stored procedure was then executed using prepared statments I am sure this is ok.

In addition to the link SQLSister posted, have a think about why this wouldn't be the case. The exec command executes whatever it is passed. And what is there to stop someone posting a drop statement in a format that would cause an injection?


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[chigley]

Ok, so you have come no farther in convincing me I am wrong.

Take for example

Create Procedure mySafeProc (@id INT)
AS
BEGIN

--DO SOME NASTY DYNAMIC SQL

END

How can you pass a nasty drop statement to that?????

Charlie Benger-Stevenson
Hart Hill IT Ltd

http://www.harthill-it.co.uk

 

[SQLSister]

Assuming all your input variables are int which is a big assumption, you can't but this is rarely the case especially when you are passing table names or fields to search on.

You made a blanket statment that just because dynamic sql was in a stored proc it was safe. That is demonstrably wrong. Yes it is still possible to write a stored proc that uses dynamic sql and prevents injection attacks, but it has to be done deliberately and with forethought and planning. It still is less secure in all cases becasue you must set permissions at the table level instead of for the proc. So users who can access the database outside the user interface can do things to your tables directly.

"NOTHING is more important in a database than integrity." ESquared

 

[chigley]

I concede the point that it is possible to write a stored procedure that is vulnerable to SQL injection attacks.

I would also encourage programmers to write ALL of their code with

forethought and planning

Charlie Benger-Stevenson
Hart Hill IT Ltd

http://www.harthill-it.co.uk

 

[ca8msm]

Ok, so you have come no farther in convincing me I am wrong.

A simple example would be:

alter procedure uspTest
(
 @myvalue varchar(30)
)
as
declare @sql varchar(max)
set @sql = 'select firstname from table1 where firstname = ''' + @myvalue + ''''
exec(@sql)

and if someone passed this in:

uspTest ''';drop table table1;select ''1'

Then the table would be dropped. Obviously this is just a very simple example, but even if you have a more complex query involving table/field names then the same security issues apply. By using this method you are seriously putting your database at risk and as the developer this is your responsibility to make sure you take whatever methods you can to protect your data.

By avoiding dynamic sql, you do not even have to think about the security implication that this method leaves you open to. You also get the advantage that the sp can be compiled and that an execution plan doesn't have to be created every time the procedure runs.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[SQLSister]

You can't plan for what you don't know is a problem and many programmer who write stored procs are not database specialists and do not know about SQL injection attacks. Therefore their code doesn't take it into account. You can argue that they should, but the real world is they do not.

Further even if they do this, the security issue of allowing users access to things they should not have access to does not go away. Lots of stolen data or deliberately destroyed data is caused by disgruntled employees. You have to protect your system from them as well as from hackers.

Dynamic SQl is also less efficient.

So what do you gain if you write a dynamic SQl proc?
* increased outside security risk (unless you spend extra development time to make sure it is thoroughly secure),
* increased inside security risk,
* worse perfomance,
* code that is difficult if not impossible to be tested against all possible permutations that it could be asked to run,
* an often increased time to maintain,
* perhaps a bit less development time (maybe not even that depending on what you are doing and how hard it is to make sure you are not vunerable to an injection attack).

I'm not saying there aren't occasions when it is useful or even needed, just like there are occasions where cursors are useful and needed. I'm saying it is a bad practice to use it unless you have no other choice.



"NOTHING is more important in a database than integrity." ESquared

 

[chigley]

Two very well reasoned pieces, and a good example of a SQL injection attack. If I could bring us back on topic now, how would you dynamically alias a column?

Charlie Benger-Stevenson
Hart Hill IT Ltd

http://www.harthill-it.co.uk

 

[SQLSister]

I would hard code it just as was suggested up the table because from the example they would not need to change.

"NOTHING is more important in a database than integrity." ESquared

 

[danvlas]

Fairly often, the sollution is sought on the server when it could be enforced in the client.

The server is too important to let you do anything. It is the client that processes the results returned by the server and it's the client's responsibility to 'format' the data.

You could write a view to extract all data in a 'raw' format and execute the select (with grouping) from the client.

conn.Exceute "Select Identifier, Count(Identifier) from ViewName Where Identifier>SomeValue Group By Identifier Order By Identifier"

would give you the same result as a procedure.

The client has the ability to construct 'dynamic' SQL, but it would be executed against a view that has only 'select' permission.

That would give you the flexibility you want and would also protect you against SQL injection attacks.

Execution would be slower than a stored procedure, but you'll have a toy to play on...



HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html