SQL to SQLI issue

[wamft]

Finally changing from SQL to SQLI for my login code, but a straight conversion doesn’t seem to work. Any suggestions? the database connection is separate, I have included below the action section when submit is pressed. I keep getting the message " The Username you supplied does not exist!" if that helps. The user is in the database.

<?php
        if($_POST['submit']){
            $username = protect($_POST['username']);
            $password = protect($_POST['password']);
            if(!$username || !$password){
                echo "<center>Please enter your <b>Username</b> and <b>Password</b>!</center>";
            }else{
                  $res = mysqli_query($GLOBALS["___mysqli_ston"], "SELECT * FROM `adminusers` WHERE `username` = '".$username."'");
                $num = mysqli_num_rows($res);
                if($num == 0){
                    echo "<center>The <b>Username</b> you supplied does not exist!</center>";
                }else{
                    $res = mysqli_query($GLOBALS["___mysqli_ston"], "SELECT * FROM `adminusers` WHERE `username` = '".$username."' AND `password` = '".$password."'");
                    $num = mysqli_num_rows($res);
                    if($num == 0){
                        echo "<center>The <b>Password</b> you supplied does not match the one for that username!</center>";
                    }else{
                        $row = mysqli_fetch_assoc($res);
                        if($row['active'] != 1){
                            echo "<center>Your login has been <b>deactivated</b>.</center>";
                        }else{
                            $_SESSION['admin_uid'] = $row['id'];
                            echo "<center>You have successfully logged in!</center>";
                              $time = date('U')+50;
                            mysqli_query($GLOBALS["___mysqli_ston"], "UPDATE `adminusers` SET `online` = '".$time."' WHERE `id` = '".$_SESSION['admin_uid']."'");
<script>location.href='http://subdomain.domain.com.au/admin/login.php'</script>");
                        }
                    }
                }
            }
        }
        ?>

[benanamen]

To be honest, every bit of that code is wrong. I would suggest you use PDO and Prepared Statements.

Here is a tutorial to get you going in the right direction.

(The only proper) PDO tutorial

[phdr]

The posted code, technically, could work. However, here’s a list of things that could cause the username message you are getting -

1. No database connection at all
2. No database connection in $___mysqli_ston
3. No selected database
4. The 1st select query is failing with an error concerning the database, table, or columns
5. The value in $_POST[‘username’] is being altered by the protect() function, so that it no longer matches an entry in the database
6. The exact value in $_POST[‘username’] isn’t actually in the database table, possibly due to some previous double escaping of data

You would need to investigate each of these things in order to pin down why the code doesn’t work.

Unfortunately, other things have changed in php, besides just the elimination of the msyql extension, such as removal of magic_quotes, that require you to make changes to your code to provide protection against sql special characters in all data types from breaking the sql query syntax. The simplest, fool-proof way of doing this is to use prepared queries. Also, the conversion tool, because it uses procedural msyqli statements allows code to continue to run upon fatal problems, because only php warnings are produced. Using OOP statements will cause fatal runtime errors for the same problems.

Rather than to rely on some conversion tool, it will be much better if you rewrite the code to use current best practices -

1. Use the much simpler and more consistent PDO extension.
2. Use prepared queries when supplying external, unknown, dynamic values to a query when it gets executed.
3. Use implicit binding with prepared queries by supplying an array to the ->execute([…]) call.
4. Use exceptions for database statement error handling and in most cases let php catch and handle the exception, requiring no logic in your code.
5. Use php’s password_hash() and password_verify(), i.e. don’t store plain-text passwords.
6. Put the post method form processing code above the start of the html document.
7. Detect if a post method form was submitted, rather than trying to test if the submit button isset or is a true value (it won’t be for some browsers, browser versions, and won’t be if you change to use ajax to submit the form.)
8. Trim, then validate all the form inputs, separately, producing unique messages for each validation error, storing user/validation error messages in an array, using the field name as the array index.
9. Do not confirm to the visitor, hacker, bot script, which of the usrename/password was not valid, since this aids bruit-force cracking attempts. Output a generic usrename/password not valid message for both values.