[dba-SQLServer] Difference between views and queries

Andy Lacey andy at minstersystems.co.uk
Thu Jun 10 16:15:40 CDT 2004


Thanks for the explanation.

Andy

> -----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 21:55
> 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
> 
> 
> 




More information about the dba-SQLServer mailing list