Select *

[Ray Allison]

Is it bad practice to use 'Select * ' from a security perspective?

Thanks for any advice.

Ray

[Danny]

In general its not a security concern unless you are restricting columns
through a view. New columns would show up (assuming the view was
refreshed). Basically select * should be avoided unless it is the intend to
expose all columns no matter what. The bigger concern is when it's embeded
in code. The code could break if columns are changed.
"Ray Allison" <ra*********@tiscali.co.uk> wrote in message
news:43**********@mk-nntp-2.news.uk.tiscali.com...

Is it bad practice to use 'Select * ' from a security perspective?

Thanks for any advice.

Ray

[Erland Sommarskog]

Ray Allison (ra*********@tiscali.co.uk) writes:

Is it bad practice to use 'Select * ' from a security perspective?

I can't see that security is much of an issue. The reason that SELECT *
is bad, is that it creates a maintenance problem. What happens if columns
are dropped, added or reordered? How can we see whether a column is
actually used?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

You never use SELECT * in production code (except in an EXISTS()
predicate). Any change to the base table will mess up the host program
that was expecting few or more columns, expose columns that you might
now want exposed, blow up VIEWs built on this query, etc.

[Trevor Best]

Danny wrote:

In general its not a security concern unless you are restricting columns
through a view. New columns would show up (assuming the view was
refreshed). Basically select * should be avoided unless it is the intend to
expose all columns no matter what. The bigger concern is when it's embeded
in code. The code could break if columns are changed.

If you have "select * from table1" in code and drop column2 how is that
different from having "select column1, column2, column3 from table1" in
code and dropping column2? (apart from the error ocurring on a different
line of code of course:-))

[pb648174]

because you would know exactly which columns were needed in the main
app, i.e. coumn1,2 and 3 as opposed to "all of them"

[Trevor Best]

pb648174 wrote:

because you would know exactly which columns were needed in the main
app, i.e. coumn1,2 and 3 as opposed to "all of them"

All that tells me is that you don't need column4.

Having "select column1, column2, column3 from table1" doesn't
necessarily mean you need column1, column2 and column3 any more or less
than "select *", the code later on will determine those columns that you
need.

You could start with "select c1,c2,c3" then end up not using c2 at all.

[Hugo Kornelis]

On Mon, 05 Dec 2005 08:33:02 +0000, Trevor Best wrote:

Danny wrote:

In general its not a security concern unless you are restricting columns
through a view. New columns would show up (assuming the view was
refreshed). Basically select * should be avoided unless it is the intend to
expose all columns no matter what. The bigger concern is when it's embeded
in code. The code could break if columns are changed.

If you have "select * from table1" in code and drop column2 how is that
different from having "select column1, column2, column3 from table1" in
code and dropping column2? (apart from the error ocurring on a different
line of code of course:-))

Hi Trevor,

Have you ever had the task to do impact analysis for an impending
change? Life is good if you can search your sources for a column name
and KNOW that you have all occurences.

Also - consider what happens if someone drops and recreates the tables
with the columns in a different order. Best case, you get an error
becuase datatypes don't match anymore. Worst case, you don't....

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Trevor Best (no****@localhost.invalid) writes:

If you have "select * from table1" in code and drop column2 how is that
different from having "select column1, column2, column3 from table1" in
code and dropping column2? (apart from the error ocurring on a different
line of code of course:-))

The difference is that
1) "SELECT column1, column2..." will bomb. A SELECT * could continue to
work, but yeild incorrect results.
2) If you are using stored procedures, you can use sysdepends to track
whether the column is used, and you would know that it is used. With
SELECT * you don't have an idea.
3) Even if you don't use stored procedure, you can still search the code
for references to the column.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Doug]

interesting. so what if there is security on a given column, say
payrolldollars that you don't want some users to see. so you limit that
access using sql security.

Will the sproc perhaps get unintended access to a web viewer for this
data???????

[J.Balakumar]

hai,
help me out of a select option that should makes to display only the
maximum number of subordinate to their immediate senior in an
organization.
like...
MD > 3 director > each has 2,3,4 person reporting subordinate
respectively.
i want to display one director have maximum 4 subordinates.
that 4 subordinates to be displayed.

empid name reporting to designation
1 aaa null MD
2 bbb aaa pm
3 ccc aaa pm
4 ddd aaa pm
5 eee bbb pl
6 f f f bbb pl
7 ggg ccc pl
8 hhh ccc pl
9 i i i ccc pl
10 j j j ddd SE
11 kkk ddd SE
12 l l l ddd SE
13 mmm ddd SE
I want to display the information of employee,whom they are DIRECTLY
Reporting to,who is having maximum number of direct subordianate.i want
to display of SE(designation) to be get display.
help me out of a select statement to view while execution.

[Erland Sommarskog]

(J.*********@gmail.com) writes:

help me out of a select option that should makes to display only the
maximum number of subordinate to their immediate senior in an
organization.
like...
MD > 3 director > each has 2,3,4 person reporting subordinate
respectively.
i want to display one director have maximum 4 subordinates.
that 4 subordinates to be displayed.

empid name reporting to designation
1 aaa null MD
2 bbb aaa pm
3 ccc aaa pm
4 ddd aaa pm
5 eee bbb pl
6 f f f bbb pl
7 ggg ccc pl
8 hhh ccc pl
9 i i i ccc pl
10 j j j ddd SE
11 kkk ddd SE
12 l l l ddd SE
13 mmm ddd SE
I want to display the information of employee,whom they are DIRECTLY
Reporting to,who is having maximum number of direct subordianate.i want
to display of SE(designation) to be get display.
help me out of a select statement to view while execution.

So there is a standard recommendation for this sort of post, and
that is that you include:

1) CREATE TABLE statment for your table(s).
2) INSERT statments with sample data.
3) The desired result given the sample.

It makes it possible to copy and paste into Query Analyzer to develop
a tested solution. It also serves to sort out any ambiguities in
your narrative. I have read your post a couple of times, but I don't
really understand what you want. Since I want to give you something
that works, I need to ask you for more detailed input.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Doug]

ttt.
if you use select *, can u allow unintended access and "bypass" the
"intent" of the security?

[Doug]

I'm rusty. I trust the peanut gallery will assist me with syntax
issues.

select employee.name, boss.name, count(employee.empid) from people as
employee
left outer join people as boss on employee.empid = boss.reportingto
where designation = 'PM'
group by employee.name, boss.name

you want the left outerjoin because sooner or later you will run into
the president of the company, who has no boss.