php app to verify login

Question

Trying to understand how to process a form in php that logs in. It seems clumsy, hopefully there is a better way.

Let's say the user login is userid and password. If they enter the information on the form, I jump to the next page:

<?php
if (isset($_POST["id"])) {
   $con=mysqli_connect("localhost","user","pw","db");
   $codeFile = $_POST["filename"];
   $id = $_POST["id"];
   $fname = $_POST["fname"];
   $lname = $_POST["lname"];
   $res = mysqli_query($con, "SELECT COUNT(*) from users where id='$id' and fname='$fname' and lname='$lname'");
   $row = mysqli_fetch_array($res);
   $count = $row[1];
   if ($count == 1) {
     header("submit.php");
     die();
   }
   $res = $con->query('INSERT INTO log values ($id, now(), $codeFile)');
}
?>

The above code should theoretically only jump to submit.php if exactly one row comes back because there is a matching user. It does not seem to work.

how do I request the first column back? It has no name because it is not a named column.
I cannot believe how many statements it takes to get one simple query done, is there any better way in PHP? Java servlets has some nifty shortcuts such as an integer return code with the number of affected lines, among other things.
if this works, I want to do an insert. It would of course be better to do a combined statement and base the test on the number of lines inserted (1 or 0)

$res = $con->query('INSERT INTO log values ($id, now(), $codeFile)');

Is there any way of combining this into a single query that returns true if it succeeds?

score 1 · Answer 1 · edited May 23 '17 at 10:29

I suggest you either use SELECT * or the actual columns themselves instead of COUNT(*)

For example: SELECT id,fname,lname from table

Yet, I suggest you go about it this way:

Instead of:

$row = mysqli_fetch_array($res); $count = $row[1];

do:

$count = mysqli_num_rows($res); if($count==1){...}

For example and adding mysqli_real_escape_string() for added security (more under Footnotes below)

Sidenote: I'm under the impression that if the query doesn't meet the criteria, that you would like users to be redirected to submit.php and if it does meet it, to do an INSERT.

If so, I modified the method. Plus, using header("submit.php"); is incorrect.

The proper way is header("Location: http://www.example.com");

Another thing before passing on to the code.

This line should use quotes around the values and double quotes to wrap it with:

$res = $con->query('INSERT INTO log values ($id, now(), $codeFile)');

as in:

$res = $con->query("INSERT INTO log values ('$id', now(), '$codeFile')");

NOTE: Try and use actual columns to insert into, it's better.

Plus, $res will not execute since there is no condition set to it. Either remove $res = or add

if($res){
echo "DB insertion was successful.";
}

The code:

<?php
if (isset($_POST["id"])) {
   $con=mysqli_connect("localhost","user","pw","db");
   $codeFile = mysqli_real_escape_string($con,$_POST["filename"]);
   $id = mysqli_real_escape_string($con,$_POST["id"]);
   $fname = mysqli_real_escape_string($con,$_POST["fname"]);
   $lname = mysqli_real_escape_string($con,$_POST["lname"]);

   $res = mysqli_query($con, "SELECT * from users WHERE fname='$fname' AND lname='$lname' AND id='$id'");

   $count = mysqli_num_rows($res);

   // if successful login, INSERT INTO...
   if ($count == 1) {

   // NOTE: Try and use actual columns to insert into, it's better.
     $res = $con->query("INSERT INTO log values ('$id', now(), '$codeFile')");

        if($res){
        echo "DB insertion was successful.";
        }
   }

   // if not successful, redirect.
   else {
     header("Location: submit.php");
     exit();
     }

} // end brace for (isset($_POST["id"]))
?>

Footnotes:

Your present code is open to SQL injection. Use prepared statements, or PDO

Passwords

I noticed that you may be storing passwords in plain text. This is not recommended.

Use one of the following:

Other links:

PBKDF2 For PHP

How about this: "INSERT INTO log VALUES (id, now(), ?) where exists (select * from users where id=? and fname=? and lname=?)" When I encrypted mysql passwords, I just did it on create: set password .. = PASSWORD('mypassword') Thereafter, I just specify the password, and it knows to encrypt it. Do I encode id the same way? — Dov, Apr 07 '14 at 18:27
You would need to use the [`bind_param()`](http://ca.php.net/manual/en/mysqli-stmt.bind-param.php) function in order to do that using the `?` placeholders. @Dov — Funk Forty Niner, Apr 07 '14 at 18:29

score 0 · Answer 2 · answered Apr 07 '14 at 04:03

You can use the mysqli->affected rows :

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

you can use LIMIT in your query to return only one result, although it's bad practice if you're getting back a login query, there should be only one anyway.

as to why your code doesn't work, It's hard to say, it depends what comes back from your database. You'll need to debug $count and see what actually comes out, and work from there.

Finalyl, as far as I know it's not possible to run two queries on the same line, You'll need two inserts for two tables.(table log and table value)

I think you misunderstood the query. I was looking up whether the user exists, and if they do, then inserting into the log table. There is only one insert. — Dov, Apr 07 '14 at 18:26

php app to verify login

2 Answers2