PHP MySQL login setup error

Question

This is all really new to me and I only know the very basics. I'm creating a frontend login for a webpage (obviously security isn't a huge deal or I wouldn't be doing it). I keep getting in issue with my "where" clause, stating that the "user" does not exist. Database is setup like this: dbname=connectivity table=users users has id, user, and pass. Anyone want to give me some pointers? Thanks in advance.

<?php
define('DB_HOST', 'localhost');
define('DB_NAME', 'connectivity');
define('DB_USER','root');
define('DB_PASSWORD','');
$con=mysql_connect(DB_HOST,DB_USER,DB_PASSWORD) or die("Ya done goofed:  " . mysql_error());
$db=mysql_select_db(DB_NAME,$con) or die("Ya done goofed: " . mysql_error());

function SignIn()
{
session_start();   
if(!empty($_POST['user']))  


{
     $query = mysql_query("SELECT *  FROM users where user = `$_POST[user]`  AND pass = '$_POST[pass]'") or die(mysql_error());
    $row = mysql_fetch_array($query) or die(mysql_error());
    if(!empty($row['user']) AND !empty($row['pass']))
    {
        $_SESSION['user'] = $row['pass'];
        echo "SUCCESSFULLY LOGIN TO USER PROFILE PAGE...";

    }
else
{
    echo "SORRY... YOU ENTERD WRONG ID AND PASSWORD... PLEASE RETRY...";
}
}
}
if(isset($_POST['submit']))
{
    SignIn();
}

?>

Answer 1

Please stop using mysql_*. use mysqli_* or PDO. Have a look to the code:-

<?php
// Force PHP to show errors
error_reporting(E_ALL); // Get all type of errors if any occur in code
ini_set('display_errors',1); // Display those errors

session_start(); // start session

define('DB_HOST', 'localhost');
define('DB_NAME', 'connectivity');
define('DB_USER','root');
define('DB_PASSWORD','');

$con = mysqli_connect(DB_HOST,DB_USER,DB_PASSWORD,DB_NAME) or die("connection not established"); Or use  $con = mysqli_connect('localhost','root','','connectivity') or die("connection not established");

if(isset($_POST['submit'])){
    SignIn();
}

function SignIn(){
   if(!empty($_POST['user'])) {
        $username = mysqli_real_escape_string($con , $_POST['user']); // prevent form SQL injection
        $password = mysqli_real_escape_string($con , $_POST['pass']); // prevent form SQL injection
        $query = mysqli_query($con,"SELECT *  FROM users where user = '".$username."'  AND pass = '".$password."'") or die(mysqli_error($con));
        if(mysqli_num_rows($query) > 0){ // check count of resultset
            $_SESSION['user'] = $_POST['pass'];
            echo "SUCCESSFULLY LOGIN TO USER PROFILE PAGE...";
        }else{
            echo "SORRY... YOU ENTERD WRONG ID AND PASSWORD... PLEASE RETRY...";
        }
    }
}
?>

Answer 2

There are some issues here:

SELECT *  FROM users where user = `$_POST[user]`  AND pass = '$_POST[pass]'

The quote styles are all over the place. Try this:

SELECT * FROM `users` WHERE `user` = '$_POST[user]' AND `pass` = '$_POST[pass]'

Also, you should pre-process for SQL injection if you're not already.

Answer 3

This is the correct formatted SQL.

$query = mysql_query("SELECT * FROM `users` WHERE `user` = `'".$_POST["user"]."'` AND pass = '".$_POST["pass"]."'") or die(mysql_error());

One thing to note is that you MUST escape and validate all global variables. For more information I strongly recommend you to read this SO post: How can I prevent SQL injection in PHP?

Answer 4

Just changed your code like follows:

SELECT * FROM users where user ='$_POST[user]'AND pass = '$_POST[pass]'

That line need to rewrite like follows:

SELECT * FROM users WHERE user = '".$_POST[user]."' AND pass = '".$_POST[pass]."'

I believe that should work in every server without any kind of trouble.

Answer 5

There are multiple things wrong with your code check it down below:

<?php
session_start(); // This needs to be on top of every page

define('DB_HOST', 'localhost');
define('DB_NAME', 'connectivity');
define('DB_USER','root');
define('DB_PASSWORD','');

// Use mysqli_* as mysql_* is depracted and will be removed
$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die("connection not established");

// Add a bit of security
$user = mysqli_real_escape_string($con, $_POST['user']);
$pass = mysqli_real_escape_string($con, $_POST['pass']);

function SignIn($user, $pass) {
    // Add backticks ` around column and table names to prevent mysql reserved word error
    $query = mysqli_query($con, "SELECT * FROM `users` WHERE `user` = '$user'  AND `pass` = '$pass'");
    // No need to fetch the data you already have
    // Check if the query returns atleast 1 row (result)
    if( mysqli_num_rows($query) >= 1 ) {
        $_SESSION['user'] = $pass;
        echo "SUCCESSFULLY LOGIN TO USER PROFILE PAGE...";
    } else {
        echo "SORRY... YOU ENTERD WRONG ID AND PASSWORD... PLEASE RETRY...";
    }
}

if(isset($_POST['submit']) && !empty($user) && !empty($pass) ) {
    SignIn($user, $pass);
} else {
    echo "SORRY... THERE ARE EMPTY FIELDS... PLEASE RETRY...";
}

?>

Answer 6

You are missing quotations

Corrected code:

$query = mysql_query("SELECT * FROM `users` WHERE `user` = `'".$_POST["user"]."'` AND pass = '".$_POST["pass"]."'") or die(mysql_error())