Error with sqlsrv_query MS SQL in PHP page

Question

Good day everyone:

I'm having trouble making a login page for my PHP application with a connection to an MS SQL Server database.

I can connect to the database using this code:

<?php
$serverName = "DESKTOP-FDJAOMJ\\sqlexpress"; //serverName\instanceName
$connectionInfo = array( "Database"=>"sgo", "UID"=>"arm", "PWD"=>"arm123");
$con = sqlsrv_connect( $serverName, $connectionInfo);

if( $con ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}
?>

And then the connection is ok. But, I've only ever worked with a MySQL backend, and this project requires MS SQL, and I can't get the MS SQL login page to work. My code is below:

<!DOCTYPE html>
<html>
    <body>
        <?php
        require('db.php');
        session_start();
        // If form submitted, insert values into the database.
        if (isset($_POST['username'])){
                // removes backslashes
            $username = stripslashes($_REQUEST['username']);
                //escapes special characters in a string
            //$username = unpack($con,$username);
            $password = stripslashes($_REQUEST['password']);
            //$password = addslashes($con,$password);
            //Checking is user existing in the database or not
            $query = "SELECT * FROM `users` WHERE username='$username' and password='$password'";
            $params = array();
            $result = sqlsrv_query($con,$query,$params) or die(sqlsrv_errors());
            $rows = sqlsrv_num_rows($result);
                if($rows==1){
                $_SESSION['username'] = $username;
                    // Redirect user to index.php
                header("Location: index.php");
                 }else{
            echo "<div class='form'>
        <h3>Username/password is incorrect.</h3>
        <br/>Click here to <a href='login.php'>Login</a></div>";
            }
            }else{
        ?>
        <div class="form">
            <h1>Log In</h1>
            <form action="" method="post" name="login">
                <input type="text" name="username" placeholder="Username" required />
                <input type="password" name="password" placeholder="Password" required />
                <input name="submit" type="submit" value="Login" />
            </form>
            <p>Not registered yet? <a href='registration.php'>Register Here</a></p>
        </div>
        <?php } ?>
    </body>
</html>

When I type in the username and password I get an error:

Notice: Array to string conversion in C:\wamp\www\PHPwebpage\login.php on line 18. line 18 is ""$result = sqlsrv_query($con,$query,$params) or die(sqlsrv_errors());"".

users table variables: id int username varchar(50) email varchar(100) password varchar(50) trn_date datetime

Nigel Ren · Answer 1 · 2018-10-26T18:05:18.810

3

Although I'm not a SQL Server coder, there are a few things you will need to change...

    $query = "SELECT * FROM [users] WHERE username='$username' and password='$password'";
    $params = array();
    $result = sqlsrv_query($con,$query,$params) or die( print_r( sqlsrv_errors(), true));

With MySQL, you can use backticks round table names and column names, but not in SQL Server - as pointed out by Parfait in the comments, you should use [] instead for reserved words.

With the error your getting - sqlsrv_errors() returns an array, so I've changed the die() to put out print_r( sqlsrv_errors(), true).

I would also recommend looking into prepared statements to avoid SQL injection and password_hash() to improve the password processing.

edited Oct 26 '18 at 18:05

answered Oct 26 '18 at 17:28

Nigel Ren

56,122
11
43
55

This helped. For whatever reason, now I'm getting a line that reads: Connection established. Array ( [0] => Array ( [0] => 42S22 [SQLSTATE] => 42S22 [1] => 207 [code] => 207 [2] => [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column name 'username'. [message] => [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column name 'username'. ) ) The query works in MS SQL Management Studio, but, not in PHP – jdavid05 Oct 26 '18 at 17:56
Can you check the connection that you are connected to the right database. If you are then can you show the table definition in your question. – Nigel Ren Oct 26 '18 at 17:59
I added some information of the users table. I am connected to the correct database. With some further work the code seems to be working, except, I keep getting a message saying that my username and password are incorrect. I've printed the username and password variables in the code, and, they match what I am looking for in the query so they should work. – jdavid05 Oct 26 '18 at 18:41
I know it's extra work, but you should look into `password_hash()`, one reference is on the answer the other is https://stackoverflow.com/questions/26536293/php-password-hash-password-verify. This will include making sure the column is long enough. – Nigel Ren Oct 26 '18 at 18:48

score 1 · Accepted Answer · answered Oct 26 '18 at 18:15

Currently, you are passing an empty array, $params into sqlserv_query() and not using qmark, ?, placeholders in your prepared statement to bind parameter values.

Consider fixing your parameterization setup and using try/catch and not raise actual errors on web page to users. Below raises the error in log files.

$username = stripslashes($_REQUEST['username']);
$password = stripslashes($_REQUEST['password']);

//Checking is user existing in the database or not
$query = "SELECT * FROM [users] WHERE username = ? and password = ?";
$params = array($username, $password);

try {
   $result = sqlsrv_query($con, $query, $params);
} catch(Exception $e) {
   error_log("$e");
}

This worked. The login page still has some issues, but, this solved my original question. Thanks — jdavid05, Oct 26 '18 at 19:03

score 1 · Answer 3 · answered Oct 26 '18 at 18:28

1

Check the table have column name username by ALT+F1 at SSMS
Your connected to right database or not (May be some where else it was overriden)

use schema name before the table name as below

$query = "SELECT * FROM dbo.[users] WHERE username='$username' and password='$password'";

answered Oct 26 '18 at 18:28

Haady Baabs

44
6

That worked. But, now I'm getting the username and password are incorrect. I've printed the values of $username and $password, they match my inputs in the login boxes. Not sure why it's saying they are wrong. – jdavid05 Oct 26 '18 at 18:36
$query = "SELECT * FROM dbo.[users] WHERE username='".$username."' and password='."$password." ' "; use the concate operate may be there are not replacing the actual query – Haady Baabs Oct 26 '18 at 18:51
use the concatenation operator after and before $username may be there are not replacing the actual query. `$query = "SELECT * FROM dbo.[users] WHERE username='".$username."' and password='".$password."' "; ` – Haady Baabs Oct 26 '18 at 19:01

Error with sqlsrv_query MS SQL in PHP page

3 Answers3