Re: Re: Secure data management

On 5 Oct 2011, at 01:13, Tommy Pham wrote: > On Tue, Oct 4, 2011 at 4:49 PM, Stuart Dallas <stuart@3ft9.com> wrote: > > On 5 Oct 2011, at 00:45, Tommy Pham wrote: > >> On Tue, Oct 4, 2011 at 4:11 PM, Stuart Dallas <stuart@3ft9.com> wrote: >> On 5 Oct 2011, at 00:04, Mark Kelly wrote: >> >> > Hi. >> > >> > On Tuesday 04 Oct 2011 at 21:39 Stuart Dallas wrote: >> > >> >> >> >> http://stut.net/2011/09/15/mysql-real-escape-string-is-not-enough/ >> > >> > Thanks. I followed this link through and read the full message (having missed >> > it the first time round), and while I find the idea of using base64 to >> > sanitise text interesting I can also forsee a few difficulties: >> > >> > It would prevent anyone from accessing the database directly and getting >> > meaningful results unless the en/decode is in triggers, or maybe stored >> > procedures. No more one-off command-line queries. >> > >> > How would you search an encoded column for matching text? >> > >> > I'd be interested in any ideas folk have about these issues, or any others >> > they can envisage with this proposal. >> >> Base64 encoding will work when the native base64 functions are available in MySQL which >> will allow you to base64 encode the data into a statement like INSERT INTO table SET field = >> FROM_BASE64("<?php echo base64_encode($data); ?>") sorta thing. I'm still not a >> massive fan of that idea given that prepared statements are an option, but it would work. >> >> -Stuart >> >> -- >> Stuart Dallas >> 3ft9 Ltd >> http://3ft9.com/ >> -- >> >> >> Inserting and updating isn't the problem. I think Mark referring to is how would that >> be implemented in this simple type of query: >> >> SELECT * FROM my_table WHERE col_name LIKE '%key word%'; >> >> If there's no viable mean to filter the data, that storage method/medium is rather >> pointless, IMHO. > > Go back and read what I wrote again. Base64 is only being used to transmit the data to MySQL - > it's being stored in the database in its decoded form. > > -Stuart > > -- > Stuart Dallas > 3ft9 Ltd > http://3ft9.com/ > > The question still applies as how would you safeguard that 'key word' transmission, > especially against SQL injection. I suppose one could do it this way: > > SELECT * FROM my_table WHERE col_name LIKE CONCAT('%', FROM_BASE64("<?php > echo base64_encode($data); ?>"), '%') > > Is the overhead worth it to warrant that kind of safeguard? That's just a simple query > with a simple search criteria. What about in the case of subselect and multi-table joins? That would indeed be logical if base64 was your chosen method of protection, but I think prepared statements are a far more elegant solution. As for the overhead I very much doubt there's much difference between that and the overhead of prepared statements. -Stuart -- Stuart Dallas 3ft9 Ltd http://3ft9.com/