Jürgen Welz
jwelz at hotmail.com
Tue Dec 20 13:33:10 CST 2005
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