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.
>> Stuart Dallas
>> 3ft9 Ltd
>> 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 Dallas
> 3ft9 Ltd
> 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.