What is wrong with my mysqli code?

Question

I am trying to embed mysqli code into my web page so I can eliminate sql injection. Here is what my code looks like right now:

$gYear = $_POST["year"];
$gYear2 = $_POST["year2"];
$gMonth = $_POST["month"];
$gSelect = $_POST["location"];

$query = $conn->prepare("SELECT $gSelect, Year FROM unemployed WHERE year BETWEEN '$gYear' AND '$gYear2' and month='$gMonth'");
$query->bind_param('ssss', $gyear, $gYear2, $gMonth, $gSelect);

$query->execute(); 
$result = $query->get_result();

while ($row = $result->fetch_object()){


// do something with gathered rows 
}

Now, once the form is submitted, I get two errors. Here is what they say:

Warning: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: Number of variables doesn't match number of parameters in prepared statement in

AND

Fatal error: Call to undefined method mysqli_stmt::get_result() in

I really don't know what my issue is. I tried to follow the rules listed in How can I prevent SQL injection in PHP?. Does anyone know what my issues are? Why am I receiving these two error messages? Any help would be greatly appreciated.

Unfortunately you have failed to follow them; your code, if it managed to get to querying the database, would be open to an injection attack. — Ignacio Vazquez-Abrams, Jul 13 '13 at 23:53
You're putting the variable values directly into the SQL query, instead of using placeholders for the parameters. When MySQLi sees the query, it's already got all the values filled in (by PHP). — jcsanyi, Jul 13 '13 at 23:54
@user2562125 Yes, that example uses `?` in the query, instead of the variable values. — jcsanyi, Jul 13 '13 at 23:56

score 0 · Answer 1 · edited Jul 14 '13 at 00:03

0

For mysqli::prepare(), you must use ? for parameters. (The code in the post uses standard PHP variable substitution into the string - this should not be done for values when using placeholders.)

I'd strongly advise against letting users choose the column that you're selecting. Use a switch() or something similar to choose the column name in code, and place it in the $column variable.

switch($_POST['location']){
  case "loc1":
    $column = "location_one";
    break;
  case "loc2":
    $column = "location_two";
    break;
  // etc, etc...
}
$query = $conn->prepare("SELECT $column, year FROM unemployed WHERE year BETWEEN ? AND ? and month=?");
$query->bind_param('sss', $gyear, $gYear2, $gMonth);

edited Jul 14 '13 at 00:03

user2246674

7,621
25
28

answered Jul 13 '13 at 23:56

maxton

392
1
6

Beyond just strongly advising, I don't think it'll even work - because it'll end up quoted with `""`, whereas column names need to be quoted with backticks. – jcsanyi Jul 13 '13 at 23:57
Well, it would have technically almost-worked the way he/she had it originally, because he/she had put `$gSelect` right in the prepared statement. – maxton Jul 13 '13 at 23:58
So the switch statement can go directly in front of my query code and all I have to do is state each location? – user2562125 Jul 14 '13 at 00:00
Yes. It can go anywhere before your prepared statement. – maxton Jul 14 '13 at 00:01
1

Added clarification about the original code and why it was failing. – user2246674 Jul 14 '13 at 00:03
$result = $query->get_result(); Is there something wrong with this statement? I am still receiving that second error message. – user2562125 Jul 14 '13 at 00:03
You have to be on PHP >= 5.3.0 and use the mysqlnd driver to use `mysqli_stmt::get_result()`. [Source: PHP Manual](http://www.php.net/manual/en/mysqli-stmt.get-result.php) – maxton Jul 14 '13 at 00:06
Okay, I am using version 5.2.14. How do I figure out what other options I have? – user2562125 Jul 14 '13 at 00:08

score 0 · Answer 2 · answered Jul 14 '13 at 00:22

You Can use PDO to prevent sql injection and it is also work for >= PHP 5.1

PDO and the PDO_SQLITE driver is enabled by default as of PHP 5.1.0. You may need to
enable the PDO driver for your database of choice;consult the documentation for 
database-specific PDO drivers to find out more about that.

PDO documentation

It is most widely used and if you know how to use mysqli then is it also easy for you

Tutorial from tutsplus

Why you Should be using PHP’s PDO for Database Access

What is wrong with my mysqli code?

2 Answers2