Re: [PHP] Re: Secure data management
>> > There is more to SQL injection than getting the quoting correct. In my > understanding, prepared statements handles the other problems associated > with queries and SQL injection beyond just quoting. Here's a good > tutorial on how this works: > > http://www.youtube.com/watch?v=bORZlmyDw0s Paul, thanks.. I always appreciate more input as I cement my understanding of all the various things to be aware of. But here my question was coming from a slightly different angle than you seemed to address. You showed in that youtube video that type casting (if I said that right) might be needed instead of mysql_real_escape_string() (in case the questionable input is numeric instead of a string), but I was asking (essentially), "when is mysql_real_escape_string() not enough for escaping *strings*?.. or, "when do we have a case where one would normally trust escaping a user-input *string* by using mysql_real_escape_string(), but where a clever hacker can get around?... by, say, using some obscure multi-byte char?" - perhaps the way Chris Shiflett shows how to hack past addslashes when using certain multi-byte character sets in his article [1]. Again (for anyone following this thread), I understand that prepared statements just remove so much of the headache.. but in case that is not available in some setup, then when does mysql_real_escape_string() fail for escaping stings (assuming the db/charset is UTF-8)? [1] http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string -Govinda -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Secure data management
On Fri, Oct 07, 2011 at 12:46:52AM -0400, Govinda wrote:
> http://stut.net/2011/09/15/mysql-real-escape-string-is-not-enough/
>
> Hi everyone
>
> I have read many many articles and blog posts in the last few days to
> bolster my (still mostly newbie) understanding of the factors that
> play in to preventing various methods of SQL injection prevention..
> and by now I am well aware that most everyone (expert) here says
> prepared statements are the most secure method of (string hacking)
> SQL-injection prevention.. even to the point of saying that one common
> (and at least previously-popular) alternative
> "mysql-real-escape-string" is "..silly and technically insecure..".
>
> I am learning and using the CodeIgniter (CI) framework for my current
> project and, not wanting to leave myself vulnerable, I read posts on
> the CI forum on this topic, to find out if I could (or needed) to use
> prepared statements in CI.. and I read one forum thread where one dev
> shows how to hack the core system of CI so that it can use PDO (for
> prepared statements) instead of the built-in ActiveRecord (or "Query
> Bindings") which apparently rely on mysql-real-escape-string. In that
> thread, the debate goes back and forth, as it does in other threads..
> and while the sentiment that prepared statements are better because
> they remove the need to keep being ahead of the char-escaping chase...
> I never did see any example of *how* mysql-real-escape-string fails.
> The only thing I ever read that does show mysql-real-escape-string
> possibly failing is in the example in this article:
>
> http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string
>
> or rather an article referred to there, here:
> http://ilia.ws/archives/103-mysql_real_escape_string-versus-Prepared-Statements.html
>
> ..which only comes up in certain circumstances.. ("[snip]..The bottom
> line while the problem is serious, it would only affect people
> changing character sets from single-byte encodings to multibyte ones.
> As long as you stay away from multibyte encodings, with the exception
> of UTF8 you should be safe...[snip]").
>
> All well and good. I just wanted to understand, as a relative newbie,
> why such bold seemingly definitive statements are made here on this
> list in recent weeks, like ".. escaping doesn't work.." ?
>
> http://marc.info/?l=php-general&m=131293616328301&w=2
> http://marc.info/?l=php-general&m=131603743606025&w=2
>
> Isn't it that it does work in most cases.. and one just needs to
> know in which cases it can fail, and how to handle things in each
> case? I totally get the point that prepared statements just remove
> so much of the head pressure of sorting all this out.. BUT when
> someone (mostly new) like me comes along and hears that
> "mysql-real-escape-string is simply not secure", then it just gives
> an unclear and incomplete picture, and creates more head pressure.
>
> Or is there definitive evidence, more recent than the above-mentioned
> articles, that shows how to hack through mysql-real-escape-string even
> in an SQL statement e.g. inserting into a UTF8 db, a properly escaped
> (mysql-real-escape-string) var?
>
> In case my post here is lacking understanding, then please forgive..
> and better yet, please explain! I post/ask because I am wanting to
> SEE the whole everything like you few experts here who really know
> exactly what everyone is talking about (or when they are just groping
> in the dark).
There is more to SQL injection than getting the quoting correct. In my
understanding, prepared statements handles the other problems associated
with queries and SQL injection beyond just quoting. Here's a good
tutorial on how this works:
http://www.youtube.com/watch?v=bORZlmyDw0s
Paul
--
Paul M. Foster
http://noferblatz.com
http://quillandmouse.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: Secure data management
On 07/10/2011 05:46, Govinda wrote: Hi everyone I have read many many articles and blog posts in the last few days to bolster my (still mostly newbie) understanding of the factors that play in to preventing various methods of SQL injection prevention.. and by now I am well aware that most everyone (expert) here says prepared statements are the most secure method of (string hacking) SQL-injection prevention.. even to the point of saying that one common (and at least previously-popular) alternative "mysql-real-escape-string" is "..silly and technically insecure..". I am learning and using the CodeIgniter (CI) framework for my current project and, not wanting to leave myself vulnerable, I read posts on the CI forum on this topic, to find out if I could (or needed) to use prepared statements in CI.. and I read one forum thread where one dev shows how to hack the core system of CI so that it can use PDO (for prepared statements) instead of the built-in ActiveRecord (or "Query Bindings") which apparently rely on mysql-real-escape-string. In that thread, the debate goes back and forth, as it does in other threads.. and while the sentiment that prepared statements are better because they remove the need to keep being ahead of the char-escaping chase... I never did see any example of *how* mysql-real-escape-string fails. The only thing I ever read that does show mysql-real-escape-string possibly failing is in the example in this article: http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string or rather an article referred to there, here: http://ilia.ws/archives/103-mysql_real_escape_string-versus-Prepared-Statements.html Hi, Please bare in mind that the above example uses the mysql object, not mysqli (the improved mysql extenstion). The above attack doesn't work in mysqli (at least not in my setup!). There is also the issue of which character set is used by mysqli_real_escape_string. To be sure issue the mysqli_set_charset() call after connecting to the db. With regards to procedure in MySQL, there is one gotcha that caught me out. There is no way to ALTER a procedure, you must DROP and reCREATE it again. This is fine in most GUIs such as Heidi SQL, as all the work is done for you, but you must realize that when you issue the DROP command it also DROPS any permissions you have on that procedure. So to alter a procedure you must DROP, CREATE and then re-create the permissions. If you have a replicated setup with multiple servers and multiple web sites accessing them, this can be allot of permissions to re-apply. In web programming (or any other sort) you must ALWAYS assume that any input is malicious. Using this principle you can then build secure applications. For example, the above article takes the $_POST input without any validation. In this case it is a username. On my sites I tell users when registering that their username can only contain certain characters. If any other characters are supplied I strip them out. I can then pass data to mysqli_real_escape_string knowing that there will be no multi-byte characters in there to trip it up. The same goes for other input, validate it all. There are built in functions to do this for you [1], or you can build your own. It is not just input to watch out for, you must also Escape your output to mitigate Cross Site Scripting (XSS)[2] and other attacks. There are many automated tools out there to help you test your applications. You wont know its breakable unless you try to break it! And if you don't, others will. I suggest you go to the OWASP site [3] (The Open Web Application Security Project) and have a look around. Regards Ian -- [1] http://php.net/manual/en/ref.filter.php [2] http://en.wikipedia.org/wiki/Cross-site_scripting [3] https://www.owasp.org/index.php/Main_Page -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Secure data management
http://stut.net/2011/09/15/mysql-real-escape-string-is-not-enough/
Hi everyone
I have read many many articles and blog posts in the last few days to bolster
my (still mostly newbie) understanding of the factors that play in to
preventing various methods of SQL injection prevention.. and by now I am well
aware that most everyone (expert) here says prepared statements are the most
secure method of (string hacking) SQL-injection prevention.. even to the point
of saying that one common (and at least previously-popular) alternative
"mysql-real-escape-string" is "..silly and technically insecure..".
I am learning and using the CodeIgniter (CI) framework for my current project
and, not wanting to leave myself vulnerable, I read posts on the CI forum on
this topic, to find out if I could (or needed) to use prepared statements in
CI.. and I read one forum thread where one dev shows how to hack the core
system of CI so that it can use PDO (for prepared statements) instead of the
built-in ActiveRecord (or "Query Bindings") which apparently rely on
mysql-real-escape-string. In that thread, the debate goes back and forth, as
it does in other threads.. and while the sentiment that prepared statements are
better because they remove the need to keep being ahead of the char-escaping
chase... I never did see any example of *how* mysql-real-escape-string fails.
The only thing I ever read that does show mysql-real-escape-string possibly
failing is in the example in this article:
http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string
or rather an article referred to there, here:
http://ilia.ws/archives/103-mysql_real_escape_string-versus-Prepared-Statements.html
..which only comes up in certain circumstances..
("[snip]..The bottom line while the problem is serious, it would only affect
people changing character sets from single-byte encodings to multibyte ones. As
long as you stay away from multibyte encodings, with the exception of UTF8 you
should be safe...[snip]").
All well and good. I just wanted to understand, as a relative newbie, why such
bold seemingly definitive statements are made here on this list in recent
weeks, like ".. escaping doesn't work.." ?
http://marc.info/?l=php-general&m=131293616328301&w=2
http://marc.info/?l=php-general&m=131603743606025&w=2
Isn't it that it does work in most cases.. and one just needs to know in
which cases it can fail, and how to handle things in each case? I totally get
the point that prepared statements just remove so much of the head pressure of
sorting all this out.. BUT when someone (mostly new) like me comes along and
hears that "mysql-real-escape-string is simply not secure", then it just gives
an unclear and incomplete picture, and creates more head pressure.
Or is there definitive evidence, more recent than the above-mentioned articles,
that shows how to hack through mysql-real-escape-string even in an SQL
statement e.g. inserting into a UTF8 db, a properly escaped
(mysql-real-escape-string) var?
In case my post here is lacking understanding, then please forgive.. and better
yet, please explain! I post/ask because I am wanting to SEE the whole
everything like you few experts here who really know exactly what everyone is
talking about (or when they are just groping in the dark).
-Govinda
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Secure data management
Hi. On Wednesday 05 Oct 2011 at 00:04 Mark Kelly wrote: > I'd be interested in any ideas folk have about these issues, or any others > they can envisage with this proposal. Thank you all for joining in here - it's been a fascinating read so far. Mark -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Secure data management
On Tue, Oct 4, 2011 at 8:01 PM, Jeremiah Dodds wrote:
> On Tue, Oct 4, 2011 at 9:25 PM, Tommy Pham wrote:
> > 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.
>
> This is true, but it should be pointed out that for a large majority
> of web applications the performance hit given by either prepared
> statements or base64 encoding is going to be miniscule compared to the
> bottlenecks already present at the db-access and network-latency
> layers. Sites that approach needing to actively worry about the
> performance hit from either method are rare, and it's doubtful that
> the solution used would be to remove the tactic, assuming the reasons
> for the approach being used are sound and still present.
>
> >
> >
> >
> >> 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?
> >
>
> The paragraph you're replying to is saying that this shouldn't be a
> pain if your code is well organized. If your codebase is sane, these
> details should be transparent to new developers. If they can't be,
> then new developers get a chance to learn things :P
>
>
My code base, being sane and KIS, wouldn't contain that base64 :P
Anyway, I've spent the last hour or so trying PoC and some metric analysis
but can't seem to get consistent results in execution speed in the MySQL
workbench testing without (restart server before each SQL statement to
prevent use of cache) and with FLUSH+RESET. Would someone, when you have
time, please see if you're getting the same? Here are the codes:
* PHP + HTML
'.$data_base64;
}
?>
* MySQL syntax + BASE64_DECODE [1]
DROP TABLE IF EXISTS test.base64;
CREATE TABLE IF NOT EXISTS test.base64 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
data_ varchar(100) NOT NULL COLLATE utf8_general_ci,
data_base64 varchar(150) NOT NULL COLLATE utf8_general_ci
);
TRUNCATE test.base64;
-- FLUSH TABLES; FLUSH PRIVILEGES; RESET QUERY CACHE;
INSERT INTO test.base64 (data_, data_base64) VALUES ('string to encode
2',BASE64_DECODE('c3RyaW5nIHRvIGVuY29kZSAy')); /* 0.046 sec */
-- FLUSH TABLES; FLUSH PRIVILEGES; RESET QUERY CACHE;
PREPARE stmt1 FROM 'INSERT INTO test.base64 (data_, data_base64) VALUES (?,
?)';
SET @a = 'string to encode 3';
SET @b = 'string to encode 3';
EXECUTE stmt1 USING @a, @b; /* 0.015 sec */
-- FLUSH TABLES; FLUSH PRIVILEGES; RESET QUERY CACHE;
INSERT INTO test.base64 (data_, data_base64) VALUES ('string to
encode','string to encode'); /* 0.015 sec */
-- FLUSH TABLES; FLUSH PRIVILEGES; RESET QUERY CACHE;
SELECT COUNT(*) FROM test.base64; /* 3 rows */
INSERT INTO test.base64 (data_, data_base64) VALUES ('string to
encode','string to encode'); DELETE FROM test.base64;'); /* error after
DELETE -> sample SQL injection */
SELECT COUNT(*) FROM test.base64; /* 0 rows */
INSERT INTO test.base64 (data_, data_base64) VALUES ('string to encode\');
DELETE FROM
test.base64;',BASE64_DECODE('c3RyaW5nIHRvIGVuY29kZScpOyBERUxFVEUgRlJPTSB0ZXN0LmJhc2U2NDs='));
SELECT COUNT(*) FROM test.base64; /* 1 row */
-- test SELECT queries against sample data table with 219,061 rows having 4
rows contains word 'pressure'
-- FLUSH TABLES; FLUSH PRIVILEGES; RESET QUERY CACHE;
SELECT * FROM test.base64_product_desc WHERE `name` LIKE CONCAT('%',
BASE64_DECODE('cHJlc3N1cmU='), '%'); /* 0.499 sec / 0.000 sec - subsequent
runs w/o FLUSH/RESET are about same time */
-- FLUSH TABLES; FLUSH PRIVILEGES; RESET QUERY CACHE;
SELECT * FROM test.base64_product_desc WHERE `name` LIKE '%pressure%'; /*
0.530 sec / 0.000 sec - subsequent runs are received from cached if not
reset */
-- FLUSH TABLES; FLUSH PRIVILEGES; RESET QUERY CACHE;
PREPARE stmt1 FROM 'SELECT * FROM test.base64_product_desc WHERE `name` LIKE
"%?%"'; /* "%?%" \'%?%\' '%?%' */
SET @a = 'pressure';
EXECUTE stmt1 USING @a; /* failed to run */
-- FLUSH TABLES; FLUSH PRIVILEGES; RESET QUERY CACHE;
Times recorded are from the initial run for both INSERT and SELECT.
[1] base64.sql attachment from http://bugs.mysql.com/bug.php?id=18861
Re: [PHP] Re: Secure data management
On Tue, Oct 4, 2011 at 9:25 PM, Tommy Pham wrote: > 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. This is true, but it should be pointed out that for a large majority of web applications the performance hit given by either prepared statements or base64 encoding is going to be miniscule compared to the bottlenecks already present at the db-access and network-latency layers. Sites that approach needing to actively worry about the performance hit from either method are rare, and it's doubtful that the solution used would be to remove the tactic, assuming the reasons for the approach being used are sound and still present. > > > >> 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? > The paragraph you're replying to is saying that this shouldn't be a pain if your code is well organized. If your codebase is sane, these details should be transparent to new developers. If they can't be, then new developers get a chance to learn things :P -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Secure data management
On Tue, Oct 4, 2011 at 6:10 PM, Stuart Dallas wrote:
>
> On 5 Oct 2011, at 02:02, Tommy Pham wrote:
>
> On Tue, Oct 4, 2011 at 5:51 PM, Stuart Dallas wrote:
>
>> On 5 Oct 2011, at 01:13, Tommy Pham wrote:
>>
>> On Tue, Oct 4, 2011 at 4:49 PM, Stuart Dallas wrote:
>>
>>>
>>> On 5 Oct 2011, at 00:45, Tommy Pham wrote:
>>>
>>> On Tue, Oct 4, 2011 at 4:11 PM, Stuart Dallas 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(">>> 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("> 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("")
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
Re: [PHP] Re: Secure data management
On 5 Oct 2011, at 02:16, Jeremiah Dodds wrote: > On Tue, Oct 4, 2011 at 8:10 PM, Stuart Dallas wrote: >> Prepared statements incur an additional hit against the DB server to prepare >> the statement. > > But only once, right? This could, of course, still be a downside > depending the nature of your app. Once per statement per request, yes. Most web apps execute one-off statements during each request, so the ability to reuse a prepared statement is not a helpful feature for that environment. -Stuart -- Stuart Dallas 3ft9 Ltd http://3ft9.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Secure data management
On Tue, Oct 4, 2011 at 8:15 PM, Tommy Pham wrote: > On Tue, Oct 4, 2011 at 6:07 PM, Jeremiah Dodds > wrote: >> >> On Tue, Oct 4, 2011 at 7:51 PM, Stuart Dallas wrote: >> > As for the overhead I very much doubt there's much difference between >> > that and the overhead of prepared statements. >> >> Probably not. As an aside, I'm really struggling to find a case where >> it'd be worth base64-encoding the queries like that unless you were >> both concerned about someone sniffing your queries over the wire and >> sure that they wouldn't think to base-64 decode them. Not to mention >> that if your grand idea to prevent eavesdropping is simple transforms, > > If that's the case, then SSL would be a better solution since it also > protects the authentication process. In then end, I still don't see base64 > as a viable solution. *nods*. I didn't mention encryption because I figured it was the obvious solution there. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Secure data management
On 5 Oct 2011, at 02:07, Jeremiah Dodds wrote: > On Tue, Oct 4, 2011 at 7:51 PM, Stuart Dallas wrote: >> As for the overhead I very much doubt there's much difference between that >> and the overhead of prepared statements. > > Probably not. As an aside, I'm really struggling to find a case where > it'd be worth base64-encoding the queries like that unless you were > both concerned about someone sniffing your queries over the wire and > sure that they wouldn't think to base-64 decode them. Not to mention > that if your grand idea to prevent eavesdropping is simple transforms, > you've got a larger problem on your hands. I don't see a reason to use base64 to solve the SQL injection problem either, especially with prepared statements available, but that doesn't mean it won't work. As far as protecting data during transit, that's what SSL is for. Base64 is not an encryption mechanism. > It *will* work, as mysql's base64 decoder won't evaluate the decoded > string as a statement, afaik, but it will also expand the size of > stuff by around 30% while having a, imo, much better solution widely > available. It will indeed increase the size of the queries, but unless you're running Facebook, LAN capacity is very rarely a bottleneck. -Stuart -- Stuart Dallas 3ft9 Ltd http://3ft9.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Secure data management
On Tue, Oct 4, 2011 at 8:10 PM, Stuart Dallas wrote: > Prepared statements incur an additional hit against the DB server to prepare > the statement. But only once, right? This could, of course, still be a downside depending the nature of your app. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Secure data management
On Tue, Oct 4, 2011 at 6:07 PM, Jeremiah Dodds wrote: > On Tue, Oct 4, 2011 at 7:51 PM, Stuart Dallas wrote: > > As for the overhead I very much doubt there's much difference between > that and the overhead of prepared statements. > > Probably not. As an aside, I'm really struggling to find a case where > it'd be worth base64-encoding the queries like that unless you were > both concerned about someone sniffing your queries over the wire and > sure that they wouldn't think to base-64 decode them. Not to mention > that if your grand idea to prevent eavesdropping is simple transforms, > If that's the case, then SSL would be a better solution since it also protects the authentication process. In then end, I still don't see base64 as a viable solution. > you've got a larger problem on your hands. > > It *will* work, as mysql's base64 decoder won't evaluate the decoded > string as a statement, afaik, but it will also expand the size of > stuff by around 30% while having a, imo, much better solution widely > available. > >
Re: [PHP] Re: Secure data management
On 5 Oct 2011, at 02:02, Tommy Pham wrote:
> On Tue, Oct 4, 2011 at 5:51 PM, Stuart Dallas wrote:
> On 5 Oct 2011, at 01:13, Tommy Pham wrote:
>
>> On Tue, Oct 4, 2011 at 4:49 PM, Stuart Dallas wrote:
>>
>> On 5 Oct 2011, at 00:45, Tommy Pham wrote:
>>
>>> On Tue, Oct 4, 2011 at 4:11 PM, Stuart Dallas 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(">> ?>") 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("> 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.
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.
-Stuart
--
Stuart Dallas
3ft9 Ltd
http://3ft9.com/
Re: [PHP] Re: Secure data management
On Tue, Oct 4, 2011 at 7:51 PM, Stuart Dallas wrote: > As for the overhead I very much doubt there's much difference between that > and the overhead of prepared statements. Probably not. As an aside, I'm really struggling to find a case where it'd be worth base64-encoding the queries like that unless you were both concerned about someone sniffing your queries over the wire and sure that they wouldn't think to base-64 decode them. Not to mention that if your grand idea to prevent eavesdropping is simple transforms, you've got a larger problem on your hands. It *will* work, as mysql's base64 decoder won't evaluate the decoded string as a statement, afaik, but it will also expand the size of stuff by around 30% while having a, imo, much better solution widely available. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Secure data management
On Tue, Oct 4, 2011 at 5:51 PM, Stuart Dallas wrote:
> On 5 Oct 2011, at 01:13, Tommy Pham wrote:
>
> On Tue, Oct 4, 2011 at 4:49 PM, Stuart Dallas wrote:
>
>>
>> On 5 Oct 2011, at 00:45, Tommy Pham wrote:
>>
>> On Tue, Oct 4, 2011 at 4:11 PM, Stuart Dallas 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(">> 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(" 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/
>
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.
Re: [PHP] 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 wrote:
>
> On 5 Oct 2011, at 00:45, Tommy Pham wrote:
>
>> On Tue, Oct 4, 2011 at 4:11 PM, Stuart Dallas 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("> ?>") 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(" 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/
Re: [PHP] Re: Secure data management
On Tue, Oct 4, 2011 at 4:49 PM, Stuart Dallas wrote:
>
> On 5 Oct 2011, at 00:45, Tommy Pham wrote:
>
> On Tue, Oct 4, 2011 at 4:11 PM, Stuart Dallas 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("> 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(""), '%')
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?
Re: [PHP] Re: Secure data management
On 5 Oct 2011, at 00:45, Tommy Pham wrote:
> On Tue, Oct 4, 2011 at 4:11 PM, Stuart Dallas 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(" ?>") 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/
Re: [PHP] Re: Secure data management
On Tue, Oct 4, 2011 at 4:11 PM, Stuart Dallas 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(" ?>") 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.
Re: [PHP] Re: Secure data management
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("") 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/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Secure data management
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. Cheers, Mark -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Secure data management
On 4 Oct 2011, at 20:30, Shawn McKenzie wrote: > On 10/04/2011 02:23 PM, Jim Giner wrote: >> I thought I knew how to do this. >> >> I have a form that collects some data fields. My script checks if magic >> quotes are off and (since they are) executes "addslashes" on each input >> field. Then I run a query to INSERT these 'slashed' vars into the database. >> But when I go to phpadmin on my site the table does not contain any slashes. >> >> Where are they going? >> >> > > The slashes escape "data" just to tell the database that those > characters are data. The database doesn't insert the slash, that would > be unwanted. Not all databases use the slash as an escape character and > for the ones that do you should use the X_real_escape_string(), like > mysql_real_escape_string() instead of addslashes() http://stut.net/2011/09/15/mysql-real-escape-string-is-not-enough/ -Stuart -- Stuart Dallas 3ft9 Ltd http://3ft9.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: Secure data management
On 10/04/2011 02:23 PM, Jim Giner wrote: > I thought I knew how to do this. > > I have a form that collects some data fields. My script checks if magic > quotes are off and (since they are) executes "addslashes" on each input > field. Then I run a query to INSERT these 'slashed' vars into the database. > But when I go to phpadmin on my site the table does not contain any slashes. > > Where are they going? > > The slashes escape "data" just to tell the database that those characters are data. The database doesn't insert the slash, that would be unwanted. Not all databases use the slash as an escape character and for the ones that do you should use the X_real_escape_string(), like mysql_real_escape_string() instead of addslashes() -- Thanks! -Shawn http://www.spidean.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
