[dba-SQLServer] Difference between views and queries

Arthur Fuller artful at rogers.com
Thu Jun 24 15:21:00 CDT 2004


SQL has many FE tools, several included and many more available either
free or cheap or expensive. Included are Enterprise Manager and Query
Analyzer, to mention only two. 3P tools are too numerous to mention, so
I'll mention only 3: SQL Programmer (Sylvain Faust), PowerDesigner
(Sybase) and Erwin (Erwin). But there are many more.

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
Charlotte Foust
Sent: Friday, June 11, 2004 3:52 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] Difference between views and queries


Yes, but since SQL doesn't have it's own FE tool, we tend to wind up
talking Access.  :-}

Charlotte Foust

-----Original Message-----
From: Jim Lawrence (AccessD) [mailto:accessd at shaw.ca] 
Sent: Friday, June 11, 2004 10:59 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] Difference between views and queries


Charlotte...I missed the word MDB.

This is the SQL list, I hope?

Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of
Charlotte Foust
Sent: Friday, June 11, 2004 9:42 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] Difference between views and queries


Right.  That's why I specified MDBs.  

Charlotte Foust

-----Original Message-----
From: Mackin, Christopher [mailto:CMackin at quiznos.com] 
Sent: Friday, June 11, 2004 7:21 AM
To: 'dba-sqlserver at databaseadvisors.com'
Subject: RE: [dba-SQLServer] Difference between views and queries


Not so for an .adp though, where you can bind a form directly to SQL
data without ODBC.

-Chris Mackin

-----Original Message-----
From: Charlotte Foust [mailto:cfoust at infostatsystems.com]
Sent: Friday, June 11, 2004 9:16 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] Difference between views and queries


Bound forms require ODBC links to be editable.  Forms (in Access MDBs)
bound to ADO recordsets are not updatable, and ADO is the only option
that avoids ODBC links to return a recordset.

Charlotte Foust

-----Original Message-----
From: Joe Rojas [mailto:JRojas at tnco-inc.com] 
Sent: Friday, June 11, 2004 4:38 AM
To: 'dba-sqlserver at databaseadvisors.com'
Subject: RE: [dba-SQLServer] Difference between views and queries


Hi Jim,

What is the argument for not using bound forms?

JR

-----Original Message-----
From: Jim Lawrence (AccessD) [mailto:accessd at shaw.ca]
Sent: Thursday, June 10, 2004 6:46 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] Difference between views and queries

Hi All:

As to all these issues I would like to dump some of my own comments. At
the risk of saying 'I told you so', I firmly believe that users should:

1. Always (or should I say only), use windows authentication and
judiciously distribute security access, to your SQLxx, to only those who
need it. Appropriate passwords and time limits. 1. Never allow access to
the data except through your program. That means NO to ODBC drivers only
ADO-OLE. 2. In 99% of cases, control is handled through SPs. 3. And the
biggy never, never use bound forms to access SQLxx data. I have been
whining about that for years, a position that M$ has also fully
supported. 4. You, as the programmer, should design your program to
carefully validate all requests and data before they are posted.

I may be already talking to the converted so you can ignore this; if
not..Get on the program.

Seeing we at in the midst of an election campaign, a good stump,
political speech is the expected. Jim


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of
Francisco H Tapia
Sent: Thursday, June 10, 2004 1:55 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Difference between views and queries


Andy,
  SQL Server is not Access on steriods, it's a diffrent engine, and thus
requires a new level of thinking towards your database engine.  It's NOT
that you couldn't, you can do anything, heck if you wanted to you can
set up your SQL Server with a blank SA password or SA as the password.
For all that matters, and avoiding all SQL Server authentication, just
use NT authentication and enable guest users :).

There have been quite a few white papers circulating on why you
"wouldn't" use dynamic sql w/ your sql server (check out
www.sqlservercentral.com, a fine resource) but the 2 very critical
factors include performance and also quite possible damage to your data.

It is entirely possible for your sql statement to read in this manner,
lets's say that you are Selecting Data from a table and you have a
combobox to help choose data, so your SQL Statment looks like this:

"Select CompanyName, CompanyAttribute1, pKey FROM tblCompany WHERE
CompanyAttribute1 = '" & me.cboMyBOX & "'", If I was a malicious user on
your system and I have direct access to tables, and if you're doing
statements like the above it is entirely plausible that you also have
"INSERT" statements thus you are giving more than just simple SELECT
access to these tables., thus with some malformed selection I can add
this in the select

'; DELETE FROM tblCompany; SELECT '

your final statement would look like this

Select CompanyName, CompanyAttribute1, pKey FROM tblCompany WHERE
CompanyAttribute1 = ''; DELETE FROM tblCompany; SELECT ''

Now your entire COMPANY table has been wiped out, while it is completely
possible to restore your db up to the minute, you've still lost some
downtime given that you had ONE bad apple in the bunch.

Besides the sql injection threats, you also suffer from a
NON-pre-compiled statement, thus your data could conceptually be
returned a lot faster if you let it, simply by creating a view or stored
procedure.  By the way just because you are using a stored procedure
does not make you completly excempt of sql injections, if you are using
dynamic sql within that procedure you are still open to these kind of
attacks and your stored procedure is always re-comiled and thus suffers
from the same performance deficits.


Andy Lacey wrote On 6/10/2004 1:27 PM:

>But, Francisco, if I was porting to SQL Server my Access app which
>builds SELECT statements dynamically all of the time for many and 
>various situations are you saying I couldn't, or shouldn't or 
>something?
>
>-- Andy Lacey
>http://www.minstersystems.co.uk
>
>
>
>>-----Original Message-----
>>From: dba-sqlserver-bounces at databaseadvisors.com
>>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
>>Francisco H Tapia
>>Sent: 10 June 2004 20:58
>>To: dba-sqlserver at databaseadvisors.com
>>Subject: Re: [dba-SQLServer] Difference between views and queries
>>
>>
>>jwcolby wrote On 6/10/2004 9:33 AM:
>>
>>
>>
>>>Can anyone explain the difference between a view and a query?  Views
>>>use a query, plus the view keyword.  I have a couple of books that I 
>>>have read the chapter on Views, but I so far haven't managed
>>>
>>>
>>to "get"
>>
>>
>>>why you wouldn't just use the query itself instead of
>>>
>>>
>>turning it into a
>>
>>
>>>view.
>>>
>>>
>>>
>>>
>>A query is a request for an Access Database, however for Sql Server
>>you would either use a View or Stored Procedure to return the data you

>>wanted... you are also able to use dynamic SQL to retrieve the
>>information you need.  ANY request given to the SQL Server engine is 
>>managed by the engine, unless you are running Remote servers (iirc).
>>
>>In Sql Server, it is TABOO, nay, GENERALLY bad practice to use dynamic

>>sql because of the implication of SQL INJECTION attacks, this poses a 
>>"real" security threat to your database. and your server.
>>
>>another reason to use a VIEW over dynamic sql is that it is
>>pre-optimized by the SQL Server Engine and thus runs faster and more 
>>efficient.  Additionally if you use Dynamic SQL then your individual 
>>users who access the server will need EXPLICIT "SELECT" permissions by

>>you, which is another 'bad' practice.  In SQL Server you make data
>>available to your users via VIEWs and Stored Procedures or some other 
>>secure way in order to protect your tables and it's data.
>>
>>ya get wot I mean?
>>
>>--
>>-Francisco
>>
>>
>>_______________________________________________
>>dba-SQLServer mailing list
>>dba-SQLServer at databaseadvisors.com
>>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>>http://www.databaseadvisors.com
>>
>>
>>
>>
>>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>
>
>


--
-Francisco


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com



This electronic transmission is strictly confidential to TNCO, Inc. and
intended solely for the addressee. It may contain information which is
covered by legal, professional, or other privileges. If you are not the
intended addressee, or someone authorized by the intended addressee to
receive transmissions on behalf of the addressee, you must not retain,
disclose in any form, copy, or take any action in reliance on this
transmission. If you have received this transmission in error, please
notify the sender as soon as possible and destroy this message. While
TNCO, Inc. uses virus protection, the recipient should check this email
and any attachments for the presence of viruses. TNCO, Inc. accepts no
liability for any damage caused by any virus transmitted by this email.
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list