Problem with MySQL select with param binding

Databases
1

Please Seniors,
I have problem with searching the database using prepared statement, i have some variables which holds ip as column name and the ip address which is 127.0.01 for local host as ip values but when i use it to query the database it shows nothing, even when there is 16 rows that has that ip address in the table.

$give = 'ip';
$givevalue = '127.0.0.1'; 

        $mygive = $con->prepare("SELECT * FROM givetable WHERE ? = ?");
	$mygive->bind_param('ss', $give, $givevalue);
	$mygive->execute();
	$mygiveresult = $mygive->get_result();
	$mygivedis = mysqli_fetch_all($mygiveresult);
	$mygive->close();

Please what am i doing wrong here, if i run those variables directly without using prepare or binding then it prints out 16 rows which is what is actually in the database but if use param nothing works.
Also i would like to know how to get the total row count of the affected rows using para binding too
something like this

$mygive = $con->prepare("SELECT COUNT(*) FROM givetable WHERE ? = ?");
2

You walk a wrong way. PDO::prepare() makes params with proper quotes. That means:

SELECT * FROM givetable WHERE 'ip' = ‘127.0.0.1’

This two constants no way equals. So, of course you have an empty result.

1 Like
3

@igor_g i already know that setting it that way works but there is no binding there and is more like a direct sql not a prepared statement.

Even if i use

 $mygive = $con->prepare("SELECT * FROM givetable WHERE ip = ?");
$mygive->bind_param('s',  $givevalue);

This code works but it left the WHERE clause vulnerable to sql injection since it was not binded. So i want to bind both the where and its value.

I know that have something like this WHERE ? = ?
Makes it look like an equal to, and which is wrong, so what should i do to bind it properly

4

Your field comes from client request?

5

I quite don’t understand, please clearify

6

The OP is not even using PDO.

7

SQL injection possible, if you use outer data to build your query. E.g. $givevalue comes with HTTP-Request: $givevalue = $_GET['givevelaue'];

If not, if you just set your params in application, prepare is not required.

So, my question is: this $give, where are it from? From HTTP-Request or what?

1 Like
8

I think, in mysqli the same thing.

9

A prepared query provides two main features -

  1. It separates the data values from the sql query syntax, so that any sql special characters in the data cannot break the sql query syntax, which is how sql injection is accomplished.
  2. It eliminates the extra communication time, between php and the database server, and the sql parsing and planning time, on the database server, in the rare cases where you execute the same query more than once. (I did a benchmark back when the mysqli extension came out, and this saves around 5% for a mass execution on an insert query.)

Database column names are not data values, they are part of the sql query syntax, specifically they are identifiers (database names, table names, and column names.) You cannot supply a column name via a prepared query place-holder.

If the column name, ip, is static, i.e. it will always be that column, you would just build the sql query statement with that column name in it. If the column name is dynamic, i.e. it is selected/specified based on an external user input, you MUST validate that it is exactly and only a permitted choice before building the sql query with it in it.

1 Like
10

Is from post user submitted form

11

Am using mysqli prepared statement the object method

12

I have done the validation and ensure it must be one of my allowed words which are my column names as well.
So if not in_array it fails validation.

But what if one of them is in array and i want to work based on the column choosen by the user

13

The only thing i can ensure it to make sure his $give contains any of the column names.
But how do you know which column was chosen?
Secondly i don’t trust my validation of if in array, i want param binding as a second layer attack proof

14

The only way you could (untested) ‘fool’ an in_array() comparison would be to take advantage of php’s number casting in comparisons. If you have columns with numerical names (never recommend) and you list the numerical columns in the array as literal numbers, rather than strings containing numbers, php will cast the input value being tested to a number, which would match a permitted column name, but the full value that you put into the sql query could also contain sql. While this involves a collection of bad practices, someone could unknowingly create this situation.

To prevent this possibility, add a level of indirection, where the selection is actually an id, starting at one, corresponding to the column names. You would then use the submitted id to give the actual column name that you put into the sql query.

15

To get to the heart of your posted problem, you cannot have dynamic column names in a prepared statement.

Before you get to deep into this project, you really should use PDO instead of Mysqli

16

Use not the value from request. Something like…

$key = array_search($requestedValue, $fields);

if ($key === false) {
    //error
}

$give = $fields[$key];
17

At this point am so convinced that am attempting something that is not in existence.

18

I think i found a walk around this, in addition to validating if keyword is in array, i will also not take the entered variable directly to the database.
E.g

$supportedWords = array('city', 'state', 'country');

// validations
if(!in_array($give, $supportedWords)){
//failed validation code function goes here
}

// Now reconvert the variables
if($give === 'city'){
   $newgive == 'city';

}elseif($give === 'state'){
   $newgive == 'state';

}elseif($give === 'country'){
    $newgive == 'country';
}else{
   $newgive = 'country';  // to make it a default column
}


// Now my SQL will now be like this
$mygive = $con->prepare("SELECT * FROM givetable WHERE $newgive = ?");
$mygive->bind_param('s',  $givevalue);

I think at this point am much safer, please if anyone spot something unsafe in the Code please indicate, Thank You Very Much.

19

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.