Interesting SQL Vulnerable Code even after sanitisation in Stackoverflow Question

Today while browsing recent questions in PHP and MySQL Section of stackoverflow, i came around 1 recent question [22 / 09 / 2013 - 10:40 IST] which i found was interesting to share with you all.

In Post, person is sanitising his variable with ' mysqli->real_escape_string '

Code:

isset($variable) ? 'variable ='.$mysqli->real_escape_string($variable) : ''; 

but nonetheless code was still vulnerable to SQL Injection. Could you find it ?

First here is Copy paste code which is in question at Stackoverflow here.

function listPlayer($player="player_guest", $group="group_guest",
$weapon="weapon_guest", $point="point_guest", $power="level_guest",
$status="status_guest") {

    $lePlayer = (isset($player) && $player != "player_guest") ?
            'player= '.$mysqli->real_escape_string($player) : '' ;

    $leGroup = (isset($group) && $group != "group_guest") ?
            'group= '.$mysqli->real_escape_string($group) : '' ;

    $leWeapon = (isset($weapon) && $weapon != "weapon_guest") ?
            'weapon= '.$mysqli->real_escape_string($weapon) : '' ;

    $lePoint = (isset($point) && $point != "point_guest") ?
            'point= '.$mysqli->real_escape_string($point) : '' ;

    $lePower = (isset($power) && $power != "level_guest") ?
            'level= '.$mysqli->real_escape_string($power) : '' ;

    $leStatus = (isset($status) && $status != "status_guest") ?
            'status= '.$mysqli->real_escape_string($status) : '' ;

    $condition_array = ( $lePlayer , $leGroup , $leWeapon , $lePoint , $lePower , $leStatus)

    $condition_stirng = implode(' and ', $condition_array);

    $query = "Select pid, name from game where ".$condition_stirng;

    $runQuery = $mysqli->query($query);

    }




So, if you are thinking where is sql injection than read below :-
Whenever any user will give below input , it will save you from mysql injection.



Code:

myhack ' or '1' = '1 

 But if hacker types below code, it can rip your code :D

Code:

-1 union select table_name, column_name from information_schema.tables limit 1,1 

 It will do the run the requested command and Voila, got the table name and once you get the table name, you can do other command too, to fetch username and password. One example would be like :-

Code:

-1 union select username, password from users

 Got Scared ? , well here is better solution than :-

function listPlayer($player="player_guest", $group="group_guest",
$weapon="weapon_guest", $point="point_guest", $power="level_guest",
$status="level_guest") {

 $lePlayer = ($player != "group_guest") ?
   "player= '".$mysqli->real_escape_string($player)."'" : "player_guest" ;

 $leGroup = ($group != "group_guest") ?
   "group= '".$mysqli->real_escape_string($group)."'" : "group_guest" ;

 $leWeapon = ($weapon != "group_guest") ?
   "weapon= '".$mysqli->real_escape_string($weapon)."'"  : "weapon_guest" ;

 $lePoint = ($point != "group_guest") ?
   "point= '".$mysqli->real_escape_string($point)."'"  : "point_guest" ;

 $lePower = ($power != "group_guest") ?
   "level= '".$mysqli->real_escape_string($power)."'"  : "level_guest" ;

 $leStatus = ($status != "group_guest") ?
   "status= '".$mysqli->real_escape_string($status)."'"  : "status_guest" ;

 $condition_array = ( $lePlayer , $leGroup , $leWeapon , $lePoint , $lePower , $leStatus)

 $condition_stirng = implode(' and ', $condition_array);

 $query = "Select pid, name from game where ".$condition_stirng;

 $runQuery = $mysqli->query($query);

}




notice new format:- 

Code:

"status= '".$mysqli->real_escape_string($status)."'"

Have send him mail to the person who has asked this question. Hopefully he learns something cool :)

Thanks.