On Tue, Oct 4, 2011 at 6:10 PM, Stuart Dallas <stu...@3ft9.com> wrote:
> > On 5 Oct 2011, at 02:02, Tommy Pham wrote: > > On Tue, Oct 4, 2011 at 5:51 PM, Stuart Dallas <stu...@3ft9.com> wrote: > >> On 5 Oct 2011, at 01:13, Tommy Pham wrote: >> >> On Tue, Oct 4, 2011 at 4:49 PM, Stuart Dallas <stu...@3ft9.com> wrote: >> >>> >>> On 5 Oct 2011, at 00:45, Tommy Pham wrote: >>> >>> On Tue, Oct 4, 2011 at 4:11 PM, Stuart Dallas <stu...@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. >>>> >>>> >>> 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. >>> >>> >> 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. >> >> > IIRC, prepared statements doesn't incur any overhead. Instead, it's > supposed to enhance performance by telling SQL to 'prepare' via > compilation. So if you're comparing performance between the overhead of > base64 vs prepared statement, then the difference would be quite clear, > especially when the table(s) is/are more than a couple hundred thousand rows > and the queri(es) are complex. This is not mention the added complexity > into the application where managing and expanding it would incur real > (developer time) overhead, IMO. > > > Prepared statements incur an additional hit against the DB server to > prepare the statement. > > The cost of using base64 in the manner suggested is minimal, regardless of > the size of the data. The MySQL query analyser is intelligent enough to know > that from_base64('xyz') is a constant expression and will therefore only > evaluate it once. > > Yes, as in your example, if you're inserting 1 row. What if: $hobbies = array('bicycling', 'hiking', 'reading', 'skiing', 'swimming'); * base64 method pseudo code: loop the $hobbies foreach ($hobbies as $hobby) INSERT INTO hobbies SET `name` = FROM_BASE64("<?php echo base64_encode($hobby); ?>") end loop * prepared statement pseudo code prepare statement INSERT INTO hobbies SET `name` = ? bind param $hobby loop the $hobbies for ($i = 0; $i < count($hobbies); $i++) $hobby = $hobbies[i]; execute statement end loop There would be a difference in performance since the the expression has to be reevaluated, including the function FROM_BASE, every time versus one time evaluation of prepared statement. > As for the added complexity, if you have SQL statements all over your code > then yes it will add a time overhead, but any codebase of a significant size > should be using a centralised API for database access such that changes like > this have a very limited scope. > Isn't that one of the major points of OOP? Still, what about new developers, having to remember that additional (and most likely unneeded) complexity, to the project which they would like to build additional modules/plugins for? > -Stuart > > -- > Stuart Dallas > 3ft9 Ltd > http://3ft9.com/ >
