How To Check Login Using Prepared Statement?

[bahaa]

Hello,

 

Does any one know how to check login using prepared statement?

 

help appreciated

[Larry]

Yes, I do. Well, in theory.

[bahaa]

Yes, I do. Well, in theory.

can you please tell me how to write the statement?

[Larry]

Given the information provided, no.

[bahaa]

Given the information provided, no.

what information do you need?

[Larry]

How about reading the forum guidelines to see what kind of information might be required here? You've not been good about abiding by the forum guidelines before, which is why I'm giving you a particular hard time with this. But if you look at your question, you should be able to think of what other information someone would be needed in order to answer your question.

 

As a contrary example, what if I were to pose this question to you: what windshield wipers do I need for my car?

[zanimation]

The question is very vague. I think Larry covers it in his book. If anything, just Google something like 'php login using prepared statements'.

 

Here's basically how I use prepared statements to handle logins (simplified and using the PDO interface):

 

<?php
    // include connection functions (houses the 'dbConnect()' function)
    require_once(PATH_TO_INCLUDES_FOLDER . 'connection_inc.php');

    if(isset($_POST['login']))
    {
         // connect to database on the read-only account and create a PDO connection object
         $conn = dbConnect('read');

         // define the sql statement with two input parameters: username and password
         $sql = 'SELECT username, password FROM users WHERE username = :username AND password = :password';

         // prepare sql statement into a statement object
         $stmt = $conn->prepare($sql);

         // encrypt password and store in a temporary variable
         $enc_pwd = sha1($_POST['password']);

         // bind parameters with user-input
         $stmt->bindParam(':username', $_POST['username'], PDO::PARAM_STR);
         $stmt->bindParam(':password', $enc_pwd, PDO::PARAM_STR);

         // execute prepared query
         $stmt->execute();

         // if there was a row selected (username and password match found in the database)
         if($stmt->rowCount())
         {
              // log in user and do whatever else you need to do on login success
         }
         // otherwise if a row wasn't selectd (no username/password match found in the database)
         elseif(!$stmt->rowCount())
         {
              // display error message and do whatever else you need to do on login failure
         }
    }
?>

 

If you're just looking for someone to spit out code that you can simply copy and paste, the code above won't work, it's just showing one way to structure your code for using prepared statements in login verification.

 

In order to answer your question better, you should provide information like:

• 
   
  
• What interface you're using to connect to your database (PDO? mysqli? mysql?, etc)
  
• Are you encrypting your passwords?
  
• Do you use salts with your encryption?
  
• What do you want to happen when the user logs in successfully?
  
• What about when they don't log in successfully?
  

 

 

We're not going to write out the entire code for you(unless someone's really generous) but we'll help you out where you're having trouble with something. Please be specific!

 

Zane

[bahaa]

How about reading the forum guidelines to see what kind of information might be required here? You've not been good about abiding by the forum guidelines before, which is why I'm giving you a particular hard time with this. But if you look at your question, you should be able to think of what other information someone would be needed in order to answer your question.

 

As a contrary example, what if I were to pose this question to you: what windshield wipers do I need for my car?

I am sorry if I did not give enough information.

I know to to write the statement, but I dont know how to check for the num rows and how to fetch it.

 

here is my code

<?php

if(isset($_POST['login'])){

$errors = array();

if(!empty($_POST['username']) && (!empty($_POST['password']))){

$username = trim($_POST['username']);

$password = trim($_POST['password']);

$password = sha1($password);

}else {

$errors[]="<p>جميع الحقول مطلوبة</p>";

}

if(empty($errors)){

$q = "SELECT username, user_id FROM users

WHERE username = ? AND pass = ? ";

$stmt = mysqli_prepare ($con, $q);

mysqli_stmt_bind_param($stmt, 'ss', $username, $password);

if( mysqli_stmt_execute($stmt)){

//

}else{

echo "<p class='error'> خطأ في إسم المستخدم و كلمة المرور.</p>";

}

}// end of empty errors

}//end of if(isset($_POST['login'])){

 

as you see the code is missing the part where to check the num rows, fetching it, and redirect the user

[HartleySan]

mysqli_affected_rows:

http://php.net/manual/en/mysqli.affected-rows.php

 

mysqli_fetch_array:

http://php.net/manual/en/mysqli-result.fetch-array.php

 

The examples below the explanation are the most immediately understandable, I think. For a prepared statement, the query itself will not really change, just the way of structuring it.

[bahaa]

mysqli_affected_rows:

http://php.net/manual/en/mysqli.affected-rows.php

 

mysqli_fetch_array:

http://php.net/manual/en/mysqli-result.fetch-array.php

 

The examples below the explanation are the most immediately understandable, I think. For a prepared statement, the query itself will not really change, just the way of structuring it.

I am not using the Object oriented style in my code.

Do you know to to check for the num rows and fetching the array not using the Object oriented style?

[Larry]

That's better, but per the forum guidelines, it'd still be best to always include the versions involved. Most importantly here, no one could answer your question without knowing the database table and columns involved. Okay, if you check out the PHP manual pages for working with stored procedures, you'll see that after executing the statement, you can do this:

 

if (mysqli_stmt_num_rows($stmt) == 1) {

 

Then, within that conditional, to fetch the returned row, you would do this:

mysqli_stmt_bind_result($stmt, $username, $user_id);
mysqli_stmt_fetch($stmt);

 

Then the $username and $user_id variables have the retrieved values.

[HartleySan]

The provided links have both the OOP syntax and the standard procedure syntax below that. For example:

 

Object oriented style

 

int $mysqli->affected_rows;

Procedural style

 

int mysqli_affected_rows ( mysqli $link )

 

And an example of the procedure style further down the page:

 

Procedural style

 

<?php

$link = mysqli_connect("localhost", "my_user", "my_password", "world");

 

if (!$link) {

printf("Can't connect to localhost. Error: %s\n", mysqli_connect_error());

exit();

}

 

/* Insert rows */

mysqli_query($link, "CREATE TABLE Language SELECT * from CountryLanguage");

printf("Affected rows (INSERT): %d\n", mysqli_affected_rows($link));

 

mysqli_query($link, "ALTER TABLE Language ADD Status int default 0");

 

/* update rows */

mysqli_query($link, "UPDATE Language SET Status=1 WHERE Percentage > 50");

printf("Affected rows (UPDATE): %d\n", mysqli_affected_rows($link));

 

/* delete rows */

mysqli_query($link, "DELETE FROM Language WHERE Percentage < 50");

printf("Affected rows (DELETE): %d\n", mysqli_affected_rows($link));

 

/* select all rows */

$result = mysqli_query($link, "SELECT CountryCode FROM Language");

printf("Affected rows (SELECT): %d\n", mysqli_affected_rows($link));

 

mysqli_free_result($result);

 

/* Delete table Language */

mysqli_query($link, "DROP TABLE Language");

 

/* close connection */

mysqli_close($link);

?>

 

More specifically, from the above example:

 

printf("Affected rows (SELECT): %d\n", mysqli_affected_rows($link));

 

I know how it's easy to get overwhelmed when you don't understand something and you frantically want an answer, but please look at the provided links carefully before saying they don't have the information you need, even though they do.