Point well taken. However, in this case it is a partial part description search from a web page, so the user could type anything. I have locked this down every way I can think of (I have to deal with it otherwise and I hate addressing the same issue more than once). There is other criteria that will always run as well, but again, a search on a partial description has to be somewhat wide open.
John -------------------------- Sent using BlackBerry ----- Original Message ----- From: owner-u2-us...@listserver.u2ug.org <owner-u2-us...@listserver.u2ug.org> To: u2-users@listserver.u2ug.org <u2-users@listserver.u2ug.org> Sent: Tue Apr 14 18:23:45 2009 Subject: RE: [U2] SQL Injection (was Select Problem) > From: Israel, John > Thanks for all the replies. Yeah, it was doing a > pattern match, but not one that I would have > expected... It's precisely due to issues like this that I recommend unaudited/unfiltered end-user input never be used in queries. This is the definition of SQL injection which plagues almost every developer using a relational database. Most MV developers are unaware of this concept or simply ignore it until something bad happens. The problem is greatest in open source code where a bad guy can easily find out exactly how the query is structured. It's less of a problem when the source isn't available - and that means most of us. Nevertheless people should be aware of the potential vulnerability. Example: You have salesreps who should only have access to order data for their own territory. Your report generation code looks something like this: MY.TERR = SALESREP.REC<33> * assume value is 123 for current rep INPUT FISCAL.PERIOD STMT = \SSELECT ORDERS\ STMT := \ WITH TERRITORY "\:MY.TERR:\"\ STMT := \ AND WITH PERIOD "\:FISCAL.PERIOD:\"\ EXECUTE STMT ... generate report The territory is hardcoded, seemingly secure. What if the input is as follows? 02" OR WITH TERRITORY NOT " The query becomes: SSELECT ORDERS WITH TERRITORY "123" AND WITH PERIOD "02" OR WITH TERRITORY NOT "" In short the rep gets a listing of all customers that do not have a null territory. Imagine what can happen if someone knows how to use EVAL. What if this is a web user entering their own user ID into textbox and you pass that input into a select so that they can see their order data? They could conceivably see order data for all of your customers. How do you fix the vulnerability? In this case, filter the input of FISCAL.PERIOD to make sure it's numeric, doesn't contain spaces or quotes, and maybe that it is in the range of 1 to 12 (or however many periods you have). Never pass user input directly into a select statement. This was off the top of my head so please forgive any issue with the details but the general point should be clear. HTH Tony Gravagno Nebula Research and Development TG@ remove.pleaseNebula-RnD.com ------- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ ------- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
