PHP bin2hex with mysql

Question

I am making an authorization channel for a webservice in PHP. In the first two lines I make a random 18 bytes hex number that I set against the user in my database and also send the same auth_token to the user on the other end. But when the user sends the same auth_token to fetch some data, the script is unable to search the database. There is some problem with hex code that is queried against in the database. Please help.

$hex = bin2hex(openssl_random_pseudo_bytes('18'));
$database->executeObject('UPDATE tbluser SET user_auth_token="'.$hex.'" WHERE user_name="'.$_POST['uid'].'"');
...
...
...
}elseif ( $_POST['query'] = "fetch" && !empty($_POST['auth_token']) ){
$token = $_POST['auth_token'];
$uid = $database->executeObject('SELECT user_id AS id FROM tbluser WHERE user_auth_token="'.$token.'"');
if (!empty($uid)){
    $fname = $database->executeObject('SELECT writer_first_name as fname FROM tblwriter WHERE user_id="'.$uid.'"')->fname;
    echo $fname;
    exit;
}else{ echo "Not Authorized"; exit; }

Always escape EVERYTHING that goes into an SQL query! You are vulnerable to SQL injection attacks. — Sven, Aug 15 '13 at 11:44
You can get a good explanation of SQL injection protection here: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Bill Karwin, Aug 15 '13 at 11:47
@AsimSiddiqui I am pretty sure it does not! You would need to parse the resulting SQL back to it's components, identify if there are any irregularities, and then reject the query. If it's THAT easy, any SQL server would have implemented it. — Sven, Aug 15 '13 at 11:49
It seems unlikely that your database class is successfully escaping `$_POST['uid']` _after_ it has already been concatenated into the query, as it could contain anything, including SQL code. Unless, that is, you have previously validated `$_POST['uid']` to be known to contain only valid characters for that field (alphanum, for example) — Michael Berkowski, Aug 15 '13 at 11:50
I think you guys are right, its something to do with escaping. I tried the same code without quotes around $token and my database class gave me an error: Unknown column \'81be8ba147095946e81aefef456e3b3110a5\' in \'where clause\' SQL=SELECT user_id AS userid FROM tbluser WHERE user_auth_token=81be8ba147095946e81aefef456e3b3110a5 — Asim Siddiqui, Aug 15 '13 at 11:59
And that's why good people provided us with PDO and other good and smart people invented ActiveRecord and ORM's to help us insert our stuff into our databases without worrying about Bobby Tables. — N.B., Aug 15 '13 at 12:04

Bill Karwin · Answer 1 · 2013-08-15T16:07:01.153

I'm guessing at your executeObject() function, but it looks like you are using it inconsistently.

The first time you call it, you expect it to return a scalar user_id.

$uid = $database->executeObject('SELECT user_id AS id FROM tbluser 
    WHERE user_auth_token="'.$token.'"');

Does $uid now contain a result set object, or an individual value for the user id on one row? I suggest $uid contains a result set.

But the second time you call executeObject(), you expect it to return an object, and you need to reference a specific field fname to get that column's value.

if (!empty($uid)){
    $fname = $database->executeObject('SELECT writer_first_name as fname 
        FROM tblwriter WHERE user_id="'.$uid.'"')->fname;
    echo $fname;
    exit;

It appears you need to use ->fname to get an individual column's value. So why did you use ->user_id in the first query?

I'm suggesting that the search for your auth token is actually succeeding, but then you search for the fname by interpolating the whole result set object from the first query, not an individual column's value. So the second query isn't searching correctly.

Unless your query result object has a very intelligent __toString() method built into it.

Thanks a lot Bill for your efforts and time. The search for auth_token is NOT succeeding. The search for the fname is working fine. I checked by breaking the script down. The reason for the auth_token not succeeding is somehow the escaping thing. But while using my database class, I somehow cannot understand how to escape it before sending it in the query. — Asim Siddiqui, Aug 15 '13 at 17:04
I don't know what database class you're using, so I can't help any further. — Bill Karwin, Aug 15 '13 at 17:15

score 0 · Answer 2 · answered Aug 15 '13 at 18:09

0

Thanks a lot everyone. The script is working perfectly. I mistakenly set the length of the user_auth_token 18 instead of 37 in the database. Thanks once again.

answered Aug 15 '13 at 18:09

Asim Siddiqui

309
5
19

PHP bin2hex with mysql

2 Answers2