How to authenticate users with credentials in MySQL database

Question

On my form page, I have two textboxes with the names name and password.

When the user hits submit, it sends that data into two columns in a MySQL database named 'name' and 'password'.

After the data is recorded (which is the part I understand and don't need help with), I want the user to be at the sign-in page and type in his/her name and password and only be allowed into the site if the name and password data already exist in the database (part that I don't understand).

Would I use the following query :

SELECT * FROM tablename WHERE name & password = "'$_POST[name]', $_POST[password]'

Use `mysqli_num_rows` for php. If you have used mysqli_* API — aldrin27, Sep 01 '15 at 01:24

score 2 · Answer 1 · edited May 23 '17 at 11:43

You should use AND or && instead of just a single ampersand (&), and separate the variables to be binded accordingly to their column name.

You should also consider sanitizing your variables before using them to your queries. You can use *_real_escape_string() to prevent SQL injections.

$name = mysql_real_escape_string($_POST["name"]);
$password = mysql_real_escape_string($_POST["password"]);

"SELECT * FROM tablename WHERE name = '".$name."' AND password = '".$password."'"

But the best recommendation that I can give to you is to use prepared statement rather than the deprecated mysql_*

if($stmt = $con->prepare("SELECT * FROM tablename WHERE name = ? AND password = ?")){ /* PREPARE THE QUERY; $con SHOULD BE ESTABLISHED FIRST USING ALSO mysqli */
  $stmt->bind_param("ss",$_POST["name"],$_POST["password"]); /* BIND THESE VARIABLES TO YOUR QUERY; s STANDS FOR STRINGS */
  $stmt->execute(); /* EXECUTE THE QUERY */
  $noofrows = $stmt->num_rows; /* STORE THE NUMBER OF ROW RESULTS */
  $stmt->close();  /* CLOSE THE STATEMENT */
} /* CLOSE THE PREPARED STATEMENT */

For securing password, you could also look at password_hash().

score 1 · Answer 2 · answered Sep 01 '15 at 01:59

Please Always use Prepared statement to execute SQL code with Variable coming from outside your code. Concatenating variable from user input into SQL code is dangerous ( consider SQL injection ), you could use prepared statement with mysqli or PDO ( recommended ).

Mysqli example:

$mysqli = new mysqli("example.com", "user", "password", "database");
// error check you connection here
$query='select * from tablename where user =? AND password=?';
$stmt = $mysqli->prepare($query);
$stmt->bind_param("ss", $user,$password);
$stmt->execute();
if($stmt->num_rows!=1) {
   // check failed
}else{
   // check success
}

PDO example (recommended )

$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
// error check you connection here
$query='select * from tablename where user =? AND password=?';
$stmt = $dbh->prepare($query);
$stmt->bindParam(1,$user);
$stmt->bindParam(2,$password);
$stmt->execute();
if($sth->fetchAll()) {
    // check success
}else{
    // check failure
}

Additionally you should also consider using some form of 1-way password encryption ( password hashing ) before storing it in your database and compare it to the hash( the most accepted way to do it is using Bcrypt).

score 0 · Answer 3 · answered Sep 01 '15 at 01:29

0

You can use something like

 
SELECT count(*) FROM tablename WHERE name  = "'.$_POST[name].' AND  password = "'. $_POST[password].'"

You should expect count to be exactly 1 - indicating valid user, 0 - indicating invalid user

Anything greater than 1 should be invalid scenario indicating some kind of inconsistency in your database...

answered Sep 01 '15 at 01:29

user1228785

512
2
6
19

3

Warning: [SQL injection vulnerability](https://en.wikipedia.org/wiki/SQL_injection#Incorrectly_filtered_escape_characters). – Pang Sep 01 '15 at 01:35
Agreed.... If question wants to look at advanced level and security. There is a lot like https , encryption of password over wire and in Database, SQL Injection etc.... – user1228785 Sep 01 '15 at 01:37

score 0 · Answer 4 · answered Sep 01 '15 at 01:30

You should assign the variables to name & pass subsequently.

You can try this:

$con = mysqli_connect("localhost","YOURUSER","YOURPASS","YOURDB");
if (mysqli_connect_errno())

{
    echo"The Connection was not established" . mysqli_connect_error();



$user
= mysqli_real_escape_string($con,$_POST['user']);

$pass = mysqli_real_escape_string($con,$_POST['password']);

$query = "select * from tablename where user ='$user' AND password='$pass' ";
$run = mysqli_query($con,$query);
$check = mysqli_num_rows($run );
            if($check == 0)
            {
                echo "<script> alert('Password or Email is wrong,try again!')</script>";

            }
            else 
            {
    //get a session for user    

$_SESSION['user']=$user;

                  // head to index.php; you can just put index.php if you like

                echo"<script>window.open('index.php?login=Welcome to Admin Area!','_self')</script>";
            }

How to authenticate users with credentials in MySQL database

4 Answers4