MySQL PDO: Invalid parameter number: parameter was not defined

Question

I'm building a login page where the username can be that of either a student or teacher, where student names are simply numeric (i.e. 45678). I've made a function account_type($user) which just returns "student" if $user is numeric, and "teacher" if otherwise.

Edit: To clarify, students will type in their SID (i.e. 12345) to log in, and so my function account_type() will determine them to be a student. As such, the MySQL query to access a student account is different than the one to access a teacher's account, wherein an email address is required.

The student log in works fine with the named parameters, but when I try using a string and looking for an email, I get the error:

Invalid parameter number: parameter was not defined

I've tried putting quotes around the :user in the query, but that didn't help. I've triple checked that the queries are right, and they work on MySQL. Any ideas what I should do? The obvious answer is to use mysqli or to not use named parameters, but I'm hoping for it to work with named parameters for more complex queries later on.

Here's my code:

try {
  $pdo = new PDO($dsn, DB_USERNAME, DB_PASSWORD);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e) {
  die("Could not connect to database. " . $e->getMessage());
}

$user = mysqli_real_escape_string($link, $_POST['user']);
$pass = mysqli_real_escape_string($link, $_POST['pass']);
$account_Type = account_type($user);
if ($account_Type == "student") {
  $query = "SELECT id, password FROM students WHERE sid = :user";
}
else if ($account_Type == "teacher") {
  $query = "SELECT id, password FROM staff WHERE email = :user";
}
$stmt = $pdo->prepare($query);

$stmt->execute([
  ':user' => $user
]);

Thanks in advance!

Why are you using `mysqli_real_escape_string` when you're also using a PDO prepared statement?! — deceze, Jun 26 '18 at 20:07
Are you using PDO or mysqli? What does `var_dump($link);` return? You shouldn't have 2 connections, and if you are you can't mix their functions. — user3783243, Jun 26 '18 at 20:07
`$link` is the result of `mysqli_connect()` with the database parameters. I've read that prepared statements are not 100% safe from edge cases, hence including the `mysqli_real_escape_string()` — Curtis Upshall, Jun 26 '18 at 20:15
@CurtisUpshall You have it backwards. Escaping isn't 100% safe, prepared statements are. — Barmar, Jun 26 '18 at 20:34
https://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection?rq=1 — Curtis Upshall, Jun 26 '18 at 20:40
That is a very obscure scenario in which you yourself have painted yourself into a corner by using the API incorrectly. The same issue can happen when using escaping instead of prepared statements. And the fix is simple and provided with that answer. **Don't use two different MySQL APIs and escaping for this non-issue!** — deceze, Jun 27 '18 at 07:33

score 0 · Answer 1 · answered Jun 27 '18 at 04:38

There are two functions:

mysqli_real_escape_string which is supported in PHP 5 and 7 and must be used with mysqli: http://php.net/manual/en/mysqli.real-escape-string.php
And mysql_real_escape_string which was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0 and you can use withou a mysqli connection: http://php.net/manual/en/function.mysql-real-escape-string.php

In the comments of your question you posted a link that uses the mysql_real_escape_string but in your code you are using the mysqli_real_escape_string

So if you are using < PHP 7 or early you can use mysql_real_escape_string to improve your security without mysqli, like this:

$user = mysql_real_escape_string($_POST['user']);

but if you are using > PHP 7 then you must use mysqli, like in the example below:

 <?php

    //create a connection
    $mysqli = new mysqli("localhost", "root", "", "school");

    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }

    //mysqli_real_escape_string is the procedural version of the function below
    $user = $mysqli->real_escape_string('1');        
    $pass = $mysqli->real_escape_string('student1');    

    //verify if is a student or teacher
    $is_student = is_numeric($user);


    if ($is_student == true) {
        $query = "SELECT sid, name, password FROM students WHERE sid =? AND password =?";
    }
    else {
        $query = "SELECT sid, name, password FROM staff WHERE email =? AND password =?";
    }

    if ($stmt = $mysqli->prepare($query)) {

        if($is_student){
            //i for integer and s for string
            $stmt->bind_param("is", $user, $pass); 
        }else{
            //s for string and s for string
            $stmt->bind_param("ss", $user, $pass ); 
        }                    

        $stmt->execute();
        $stmt->bind_result($sid, $name, $password);

        $stmt->fetch();
        printf("sid: %s; name: %s; password: %s\n", $sid, $name, $password);
        $stmt->close();
    }

Hope this helps.

MySQL PDO: Invalid parameter number: parameter was not defined

1 Answers1