MSSql returns only Resource id #3 response on echo sqlsrv_query()

Question

Getting the same kinda issue with all of my apis.. On echo sqlsrv_query(); gives the response Resource id #3. Also cant find any way to see whether the data returned is empty or not.. May be because of the Resource id #3 response my query data is hampered. Please do help.

Below is my code.

<?php
// the connection part of the code.......
$connectionInfo = array( 
    "UID"=>$uid,"PWD"=>$pwd,"Database"=>$databaseName,"CharacterSet" => "UTF-8");   

/* Connect using SQL Server Authentication. */    
$conn = sqlsrv_connect( $serverName, $connectionInfo); 
if( $conn === false ) {
 die( print_r( sqlsrv_errors(), true));
}

$username= $_REQUEST['username'];
$password= $_REQUEST['password'];

if($username && $password)
{
    $query = "SELECT NAME,LOGINID,PASSWORD FROM SMS_ACCESS WHERE LOGINID = '$username' AND   PASSWORD = '$password'";
    /* Execute the query. */    
    $stmt = sqlsrv_query( $conn,$query);    
    $json = array(); 

    echo $stmt;

    if ( $stmt === false)    
    {       
        $json["status_code"] = "203";
        $json["status"] = "Error in execution";
            //echo "Error in statement execution.\n";    
        die( print_r( sqlsrv_errors(), true));    
        echo json_encode(['data'=>[$json]]);
    }     
    else     
    {    
        while($row = sqlsrv_fetch_array( $stmt,  SQLSRV_FETCH_ASSOC)) 
        {
            $json["name"] = $row['0'];
        }
            //echo json_encode(['status_code' => "200",'status' => "success",'data'=>[$json],'Msg' => "Login Successful !!"]);
    }    
}
else {
    $json["status_code"] = "203";
    $json["status"] = "Parameter Missing !!";   
    die( print_r( sqlsrv_errors(), true));  
    echo json_encode(['data'=>[$json]]);
}

/* Free statement and connection resources. */    
sqlsrv_free_stmt($stmt);    
sqlsrv_close( $conn);    
?>

Answer 1

If you read the documentation you'll find that sqlsrv_query returns "a statement resource on success and FALSE if an error occurred." You can't print a resource, so this behaviour is expected.

Next, you aren't actually outputting any JSON, which might explain why you're having problems with the APIs. die stops program execution immediately.

If you were outputting JSON, you're sending it with the wrong content type, and you're sending it in an inconsistent format. The error conditions have status and status_code inside a data element, while success does not.

You're fetching your database query as an associative array but then trying to use a numeric index to get a column value. As well, you don't need to loop over the result if you're only getting a single row.

Finally, you open yourself up to SQL injection by blindly inserting user-provided data into your query. This is a very bad idea, and will allow anyone to log in without authentication.

Try something like this instead:

<?php
$connectionInfo = [ 
    "UID"          => $uid,
    "PWD"          => $pwd,
    "Database"     => $databaseName,
    "CharacterSet" => "UTF-8",
];

$conn = sqlsrv_connect($serverName, $connectionInfo); 
if ($conn === false) {
    $data = [
        "status_code" => "203",
        "status"      => "Database error",
    ];
    header("Content-Type: application/json");
    echo json_encode($data);
    exit;
}

$username= $_REQUEST['username'];
$password= $_REQUEST['password'];

if(empty($username) || empty($password)) {
    $data = [
        "status_code" => "203",
        "status"      => "Parameter Missing !!",
    ];
    header("Content-Type: application/json");
    echo json_encode($data);
    exit;
}

$query = "SELECT NAME FROM SMS_ACCESS WHERE LOGINID = ? AND PASSWORD = ?";
$parameters = [$username, $password];
$stmt = sqlsrv_query($conn, $query, $parameters);
if ($stmt === false) {       
    $data = [
        "status_code" => "203",
        "status"      => "Error in execution",
    ];
    header("Content-Type: application/json");
    echo json_encode($data);
    exit;
}
$row = sqlsrv_fetch_array($stmt);
$data = [
    "status_code" => "200",
    "status"      => "success",
    "data"        => [
        "name"    => $row[0]
    ],
];
header("Content-Type: application/json");
echo json_encode($data);

sqlsrv_free_stmt($stmt);    
sqlsrv_close($conn);