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 > > >