i check password of users against the db.
what is faster check it in mysql MD5 function
... pwd = MD5('.$pwd.')
OR in PHP
... pwd = '.md5($pwd).'
or what is The Right Way Between two options ?
thanks
views:
1814answers:
5
+4
A:
I don't know which is faster, but if you do it in PHP you avoid the possibility of SQL injection.
Kip
2009-07-26 13:13:21
In any case I make mysql_real_escape_string on my $pwd params .
Haim Evgi
2009-07-26 13:22:24
This has nothing to do with it. A prepared statement would fix this without moving the MD5 function, also you're relying on the PHP MD5 function to "escape" the password.
Draemon
2009-07-26 13:22:54
@Draemon: i didn't say there weren't other ways around it, but the example doesn't give any indication of escaping the parameter. @hiam: if you decide to do it in php, you shouldn't escape the password string first. php's md5 function can take any string, and it's guaranteed to return ^[0-9a-f]{32}$
Kip
2009-07-27 04:03:15
+1
A:
Is performance really an issue here? It's likely to be marginal.
- Doing it in MySQL makes the DB do more work, which is a good thing
- Doing it in MySQL means the cleartext password gets passed further along (and the DB connection is often unencrypted).
- This has nothing to do with SQL injection. You could fix the first version without moving the MD5 function. Also if there was a bug in PHP's MD5 function there's still a possibility of an injection attack.
Draemon
2009-07-26 13:21:17
What bug in md5 function? Are you expecting to somehow exploit it and instead of 32 hexadecimal digits output some special characters and well formed sql statement? Not gonna happen.
lacop
2009-07-26 13:51:14
That's exactly the sort of attitude that leads to exploits. You shouldn't use arbitrary functions and hope they sanitise your data. What if someone removes the MD5 requirement at some point - are they going to realise the data isn't safe?
Draemon
2009-07-26 15:14:16
This is just silly, you can't then trust even any escaping function, or to bring it to extreme any function at all. Even php itself or mysql can be buggy. So only way to avoid exploits is to not write any code.
lacop
2009-07-26 15:54:37
I said *arbitrary* function. An escaping function is not arbitrary. If you change/remove a call to such a function you should know you might be exposing a flaw. It would not be obvious to me that removing/changing a call to md5() could introduce an injection issue. The correct way to handle this is to make sure all user input is sanitized by functions *specifically designed* to avoid vulnerabilities, and that applies equally to both cases - thus injection vulnerability is not an issue pertinent to the OP.
Draemon
2009-07-27 14:07:51
How is having the DB do more work a good thing? Getting another web server to handle more load is easy. Getting another DB server makes things complicated.
Echo
2010-04-22 14:34:29
@Echo: You're right, I didn't really mean "do more work" in a computational sense, more that you're encapsulating the storage format in the DB, which in this case is a good thing. I really didn't make that very clear.
Draemon
2010-04-24 09:59:11
+11
A:
If your application is only calcullating md5 when someone registers on your site, or is logging in, own many calls to md5 will you do per hour ? Couple of hundreds ? If so, I don't think the really small difference between PHP and MySQL will be significant at all.
The question should be more like "where do I put the fact that password are stored using md5" than "what makes me win almost nothing".
And, as a sidenote, another question could be : where can you afford to spend resources for that kind of calculations ? If you have 10 PHP servers and one DB server already under heavy load, you get your answer ;-)
But, just for fun :
mysql> select benchmark(1000000, md5('test'));
+---------------------------------+
| benchmark(1000000, md5('test')) |
+---------------------------------+
| 0 |
+---------------------------------+
1 row in set (2.24 sec)
And in PHP :
$before = microtime(true);
for ($i=0 ; $i<1000000 ; $i++) {
$a = md5('test');
}
$after = microtime(true);
echo ($after-$before) . "\n";
gives :
$ php ~/developpement/tests/temp/temp.php
3.3341760635376
But you probably won't be calculating a million md5 like this, will you ?
(And this has nothing to do with preventing SQL injections : just escape/quote your data ! always ! or use prepared statements)
Pascal MARTIN
2009-07-26 13:23:29
A:
I would say, read the column value out of mysql, then compare the result with the computed hash in your client code (e.g. php).
The main reason for doing this is that it avoids stupid things such as the database collating the column in a non-binary fashion (e.g. case-insensitive etc), which is generally undesirable for a hash.
MarkR
2009-07-26 21:45:55