Using select exists () to compare passwords in postgres with php

Question

I'm using the following postgresql command to try to retrieve a record for a given email / hashed pw combination. I have the password and email stored in the table, and the query seems to successfully pull f or t for the password check, but the php script doesn't seem to be working to handle the output.

PHP:

    // 2. Check Passwords
    $sql = "select exists(select 1 from users where (email='".$email."') AND (pw = '".$password."'));";
    echo $sql . "<br>";

    $ret = pg_query($db, $sql);
       if(!$ret){
          echo pg_last_error($db);
          exit;
       }
    if($ret === true){
            echo "Login Successful";

    }else{
            $errors = $errors . "Passwords do not match. Please try again. <br>";
    }

This currently outputs the sql query

select exists(select 1 from users where (email='alex@email.com') AND (pw = '8a8bA3anjqV.g'));

which returns

     exists
--------
 t
(1 row)

successfully with either t or f.

I can't seem to figure out why this isn't working with my php script. (It fails for all results (t or f))

Any thoughts would be hugely appreciated.

Do note that your code is wide open for SQL injection. You should be using parameterized queries if possible. See [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) - note that the information may be a little different since you're using pgSQL. — Bytewave, Feb 14 '17 at 19:59
Sanitized it earlier in this same program... Do I need to be sanitizing at the database query level? — itchyspacesuit, Feb 14 '17 at 21:21
Parameterized queries are safer, as the database doesn't have to do any escaping and potentially miss out on some exploit the developers didn't account for. It doesn't execute the entire statement as one full SQL query, it prepares a query that data is then bound to, effectively eliminating SQL injection. — Bytewave, Feb 14 '17 at 21:23

score 0 · Answer 1 · answered Feb 14 '17 at 19:52

Aha!

I was being stupid with my php.

I revised the code as follows and it seems to work well now.

    // 2. Check Passwords
    $sql = "select exists(select 1 from users where (email='".$email."') AND (pw = '".$password."'));";
    echo $sql . "<br>";

    $ret = pg_query($db, $sql);
       if(!$ret){
          echo pg_last_error($db);
          exit;
       }
    $bool = pg_fetch_row($ret);
    echo $bool['0'] . "<br>";
    if($bool['0'] === "t"){
            echo "Login Successful";

    }else{
            $errors = $errors . "Passwords do not match. Please try again. <br>";
    }

It seems that the boolean isn't readily available and you need to extract the array row from the return result, then access the zeroth element to get the t / f. Also, it doesn't seem to be a Boolean (=== true fails even when the value is "t").

Hope this helps someone!

There is an extension made for that with live examples: https://www.postgresql.org/docs/9.5/static/pgcrypto.html — greg, Feb 15 '17 at 20:54

Using select exists () to compare passwords in postgres with php

1 Answers1