MartyConnelly
martyconnelly at shaw.ca
Tue Dec 20 20:20:31 CST 2005
Here are a couple of articles on Dynamic and Static SQL that cover some parts of SQL injection Dynamic SQL in your Stored Procedures is vulnerable to SQL Injection http://www.sommarskog.se/dynamic_sql.html and rest of site http://www.sommarskog.se/index.html ? Welz wrote: > Marty: I currently process all my parameter input boxes for delimiters > ensuring they are paired. I wrote a function a few years ago that > deals with quotes, apostrophes, pipes and '#' signs. I'll have to add > processing for '--' and maybe do a bit more research to make sure I've > got the bases covered. SQL Injection is fairly new territory for me. I > guess I'll have to see what restrictions the IT boys are going to put > on me. > > I guess I could use outer joins on all potential linked tables and > default all parameters to return everything, but this doesn't seem to > me to be a very viable solution. I have a few reports that run > painfully slowly when I pull in an (Access BE) address table, but the > reports that don't require the address are lightning fast in > comparison. Processing multiple complex joins seems to take a lot of > horsepower in certain cases in Access and I assume the same applies to > other relational database engines. > > Arthur: How do you handle the case where you allow users to search for > a Project where you know the ProjectName includes the word 'School', > but you get 400 hits in your results. Then you add a parameter for the > Contractor (joined through an Estimate-ContractorBid table) to get it > down to a dozen or so. Then you join the Employee table/EmployeeType > table to Employee Project junction table. Or you may know part of an > address, or a contact joined through Company Contact table through > Contractor Bid? Or by the fact that a PO was written in March on this > job, or we just received partial payment on an invoice where we know > only that the invoice was issued in September. Right now, my users are > accustomed to narrowing down the possibilities by adding any number of > remotely connected parameters and losing this kind of capability will > not be acceptable. > > Ciao > Jürgen Welz > Edmonton, Alberta > jwelz at hotmail.com > > > > > >> From: MartyConnelly <martyconnelly at shaw.ca> >> Reply-To: Access Developers discussion and problem >> solving<accessd at databaseadvisors.com> >> To: Access Developers discussion and problem >> solving<accessd at databaseadvisors.com> >> Subject: Re: [AccessD] Upsize? >> Date: Tue, 20 Dec 2005 02:40:17 -0800 >> MIME-Version: 1.0 >> Received: from databaseadvisors.com ([209.135.140.44]) by >> bay0-mc10-f12.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.211); >> Tue, 20 Dec 2005 02:40:31 -0800 >> Received: from databaseadvisors.com (databaseadvisors.com >> [209.135.140.44])by databaseadvisors.com (8.11.6/8.11.6) with ESMTP >> id jBKAeMJ15606;Tue, 20 Dec 2005 04:40:22 -0600 >> Received: from pd3mo3so.prod.shaw.ca >> (shawidc-mo1.cg.shawcable.net[24.71.223.10])by databaseadvisors.com >> (8.11.6/8.11.6) with ESMTP id jBKAeJJ15584for >> <accessd at databaseadvisors.com>; Tue, 20 Dec 2005 04:40:19 -0600 >> Received: from pd2mr8so.prod.shaw.ca (pd2mr8so-qfe3.prod.shaw.ca >> [10.0.141.11])by l-daemon (Sun ONE Messaging Server 6.0 HotFix 1.01 >> (built Mar 152004)) with ESMTP id <0IRS00FYLLN6GO40 at l-daemon> >> foraccessd at databaseadvisors.com; Tue, 20 Dec 2005 03:40:18 -0700 (MST) >> Received: from pn2ml3so.prod.shaw.ca ([10.0.121.147])by >> pd2mr8so.prod.shaw.ca (Sun ONE Messaging Server 6.0 HotFix 1.01(built >> Mar15 2004)) with ESMTP id <0IRS00IRJLN65HA0 at pd2mr8so.prod.shaw.ca> >> foraccessd at databaseadvisors.com; Tue, 20 Dec 2005 03:40:18 -0700 (MST) >> Received: from shaw.ca ([24.108.225.57])by l-daemon (Sun ONE >> Messaging Server 6.0 HotFix 1.01 (built Mar 152004)) with ESMTP id >> <0IRS00DKPLN5QUF0 at l-daemon> foraccessd at databaseadvisors.com; Tue, 20 >> Dec 2005 03:40:18 -0700 (MST) >> X-Message-Info: LGjzam7y+LvfI0YnrhzrabJPJzpZCIDIzI6RsR6i4OQ= >> X-Accept-Language: en-us, en >> References: <59A61174B1F5B54B97FD4ADDE71E7D0116A16E at ddi-01.DDI.local> >> User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; >> rv:1.0.2)Gecko/20021120 Netscape/7.01 >> X-BeenThere: accessd at databaseadvisors.com >> X-Mailman-Version: 2.1.6 >> Precedence: list >> List-Id: Access Developers discussion and problem >> solving<accessd.databaseadvisors.com> >> List-Unsubscribe: >> <http://databaseadvisors.com/mailman/listinfo/accessd>,<mailto:accessd-request at databaseadvisors.com?subject=unsubscribe> >> >> List-Archive: <http://databaseadvisors.com/pipermail/accessd> >> List-Post: <mailto:accessd at databaseadvisors.com> >> List-Help: <mailto:accessd-request at databaseadvisors.com?subject=help> >> List-Subscribe: >> <http://databaseadvisors.com/mailman/listinfo/accessd>,<mailto:accessd-request at databaseadvisors.com?subject=subscribe> >> >> Errors-To: accessd-bounces at databaseadvisors.com >> Return-Path: accessd-bounces at databaseadvisors.com >> X-OriginalArrivalTime: 20 Dec 2005 10:40:31.0509 (UTC) >> FILETIME=[CCEB2450:01C60551] >> >> SQL injection is the problem. >> >> Michael Maddison wrote: >> >> >Hi Jürgen, >> > >> >When faced with the same problem I went dynamic. Every other option >> just >> >as you say looks ugly. >> >I never found a good alternative, no one has offered one this time >> either. >> >It seems to me that in situations like this the 'developers' go with >> dynamic SQL, the dba's moan ;-) >> > >> >cheers >> > >> >Michael M >> > >> > >> > >> >Michael: >> > >> >With variable joins, do you point somthing like a list source of >> search 'hits' to different queries, one query for each join, or how >> do you handle variable combinations of joins? Lets say there is 1 >> table that may be joined to 0 to 5 other tables in various >> combinations, being 32 possible querydefs. I've always constructed >> the SQL in code and was very satisfied with the performance. Add >> another table and you're up to 64 querydefs. >> >That's ugly. >> > >> > >> > >> >Ciao >> >Jürgen Welz >> >Edmonton, Alberta >> >jwelz at hotmail.com >> > >> > >> > >> > >> > >> > >> > >> >>From: "Michael Maddison" <michael at ddisolutions.com.au> >> >> >> >> Hi Jürgen, >> >> >> >>If you go with variable parameters check out the 'With Recompile' >> option. >> >>It forces a new execution plan each time the procedure is run and >> >>overcomes SQL's 'parameter sniffing' problem. >> >> >> >>cheers >> >> >> >>Michael Maddison >> >> >> >>DDI Solutions Pty Ltd >> >>michael at ddisolutions.com.au >> >>Bus: 0260400620 >> >>Mob: 0412620497 >> >>www.ddisolutions.com.au >> >> >> >> >> > >> > >> > >> > >> >> -- >> Marty Connelly >> Victoria, B.C. >> Canada >> >> >> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com > > > >------------------------------------------------------------------------ > >No virus found in this incoming message. >Checked by AVG Free Edition. >Version: 7.1.371 / Virus Database: 267.14.1/207 - Release Date: 19/12/2005 > > -- Marty Connelly Victoria, B.C. Canada