JSON result format

Question

<?php
include 'Connection.php';

if(isset($_REQUEST["insert"])) 
{ 
$user = $_GET['user'];
$pwd = $_GET['pass'];

$sql = "select RegNo,UserName,password from Std_Reg where Username= '$user' and Password = '$pwd'";
//$sql = "select * from Std_Reg";
$stmt = sqlsrv_query($conn, $sql);



$result = array(); 

do {
    while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){
       $result[] = $row; 
    }
} while (sqlsrv_next_result($stmt));

if(count($result)>0)
{
    $result1['status']=1;//"Login successfully";
    array_push($result,$result1);
}
else
{

    $result1['status']=0;//"Record not found";
    array_push($result,$result1);
}
sqlsrv_free_stmt($stmt);
sqlsrv_close($conn); //Close the connnectiokn first

echo json_encode($result); //You will get the encoded array variable
}
?>

This gives me:

[{"RegNo":"xyz","UserName":"abc","password":"123"},{"status":1}].

I need:

[{"status":1},{"RegNo":"xyz","UserName":"abc","password":"123"}].

how can I get the above result? What should I change in the PHP file?

**Never store plain text passwords!** Please use ***PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html)*** to handle password security. If you're using a PHP version less than 5.5 you can use the `password_hash()` [compatibility pack](https://github.com/ircmaxell/password_compat). ***It is not necessary to [escape passwords](http://stackoverflow.com/q/36628418/1011527)*** or use any other cleansing mechanism on them before hashing. Doing so *changes* the password and causes unnecessary additional coding. — Jay Blanchard, Nov 09 '17 at 13:51
You also are open to SQL injections, which paired with plain text passwords is a big problem. — chris85, Nov 09 '17 at 13:52
Both JSON strings mean the same the order doesn't matter.. so you dont have to fix it. — Raymond Nijland, Nov 09 '17 at 13:59
a better JSON structure would be `{ "status": 1, "data": [{"RegNo":"xyz","UserName":"abc","password":"123"}] }`. Right now it looks to the person viewing the JSON as if the "status" object is a row from the DB, when it isn't. It'd be hard to differentiate them and also irritating when looping through or binding the results if one item always had to be ignored. So I think you would be better to re-structure it so it's clear the "status" is a separate piece of information from the actual payload of data. — ADyson, Nov 09 '17 at 15:06

score 1 · Answer 1 · answered Nov 10 '17 at 05:13

Much is wrong here. Starting from the top:

You are including the user's password in the URL, leaving it in their browser history and many other places
You are inserting user-supplied data into a database query without sanitizing it, you are wide open for SQL injection attacks
You are storing plaintext passwords in the database, which makes it easy for an attacker to get them with the aforementioned injection attack
You are looping through multiple result sets though you only have one
If you're pulling a single user record you don't really need to put in a while loop at all
You are sending your JSON with an incorrect MIME type
JSON is a data transfer format, it shouldn't matter what order the elements are in. If it does matter, somebody has done something wrong.

Try something like this instead:

<?php
include 'Connection.php';

if(isset($_REQUEST["insert"])) {
    // we are using POST and not GET
    $user   = $_POST["user"];
    $pwd    = $_POST["pass"];
//    uncomment this once your passwords are stored securely
//    $pwd    = password_hash($_POST["pass"], PASSWORD_BCRYPT);
    $sql    = "SELECT RegNo, UserName, password FROM Std_Reg WHERE Username = ? AND Password = ?";
    $params = array($user, $pwd);
    // see how the parameters are passed separately and replace the ? in the query
    $stmt   = sqlsrv_query($conn, $sql, $params);
    // we can check for rows before looping through the result set
    if (sqlsrv_has_rows($stmt)) {
        // this is how to append to an array, array_push() is not PHP-like
        $result[] = array("status" => 1);
        while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){
            $result[] = $row; 
        }
    } else {
        // note also the [] construct automatically creates the variable
        $result[] = array("status"=>0);
    }
    sqlsrv_free_stmt($stmt);
    sqlsrv_close($conn);
    // this isn't just plain text
    header("Content-Type: application/json");
    echo json_encode($result);
}
?>

JSON result format

1 Answers1