CODEWITHSUNDEEP
mysqlibindparam
Dennis
Dennis

Reputation: 187

Mysqli bind_param() doesnt work as expected

Can anybody tell me what I'm doing wrong? When I execute and fetch this I'm becoming the correct Result.

$message = '100';
if($stmt = $mysqli->prepare("select english from table where message=? LIMIT 1")) {
$stmt = bind_param("i", $message);
} //Return the Message in English

But when I put the language into a variable and use bind_param(). I just get the new variable as a result back ex.:

$message = '100';
$language = 'english';
if($stmt = $mysqli->prepare("select ? from table where message=? LIMIT 1")) {
$stmt = bind_param("si", $language, $message);
} //Returns "english"

I guess the Problem is that the "$language" is binded as a "String". What would be the correct type? Tried allready to change it to blob or double with no result.

Upvotes: 0

Views: 68

Answers (1)

Sasha
Sasha

Reputation: 4004

Let's look what does bind_param do.

$stmt = $mysqli->prepare("select * from users where name=?");
$stmt->bind_param("s", "Dennis");

is roughly equivalent to

$stmt = $mysqli->prepare("select * from users where name='Dennis'");

However, using bind_param is much safer than inserting value by hands into SQL-query. Because when inserting value by hands, you need to carefully handle special characters; otherwise maliciously-constructed value can make SQL-parser to consider parts of the value not as value, but as commands, table names, column names, etc. The bind_param does it for you; with bind_param you don't hane to worry about that Dennis should be turned into 'Dennis' before inserting into SQL-query, but Dennis O'Brian should turn into 'Dennis O\'Brian' (not 'Dennis O'Brian'), and NUL-character should turn into '\0' (not remain as NUL-character).

The bind_param is suitable only for inserting values into SQL-query. Because when you need to insert column or table name into SQL-query, you actually need the opposite -- you need column name to be inserted into SQL-query directly (without adding any quotes, etc). So you actually need:

select english from table where message=... LIMIT 1

not

select 'english' from table where message=... LIMIT 1

So, just do it:

$message = '100';
$language = 'english';
$sql = "select " . $language . " from table where message=? LIMIT 1";
if($stmt = $mysqli->prepare($sql)) {
    $stmt->bind_param("i", $message);
}

The only thing is that you must be sure than $language contains valid column name. Otherwise, if user forced something bad to be passed through $language, you'll get SQL-injection.

$message = ...;
$language = ...;
...;
if(!in_array($language, array('english', 'german', 'french'), true))
    $language = 'english';
$sql = "select " . $language . " from table where message=? LIMIT 1";
if($stmt = $mysqli->prepare($sql)) {
    $stmt->bind_param("i", $message);
}

Upvotes: 1

Related Questions