Login script - switch to prepared statement

mysql query - how to?

[bethesda]

Hi, i try to figure it out, how to change normal mysql_query to prepared statement. My code is:

$user = strtolower($_POST['user']);
$pass = mysql_real_escape_string($_POST['pass']);
$pwd = md5($pass);
$query = "SELECT user, confirmed, adm FROM usr WHERE user = '$var1' AND pass = '$var2'";
$result = mysql_query($query);

if (!$result || mysql_num_rows($result) < 1){
$feedback = 'wrong entry, try again';
return $feedback;
} else {
if (mysql_result($result, 0, 'is_confirmed') == '1') {
user_set_tokens($user);
return 1;
} else {
$feedback = 'This account is not validated';
return $feedback;}

Now, i try to make same thing but with prepared statement.

$db = new mysqli('localhost', 'username', 'password', 'database');
$stmt = $db->stmt_init();
if($stmt->prepare("SELECT `user`, `confirmed`, `adm` FROM `usr` WHERE `user` = ? AND pass = ?")) {
$stmt->bind_param('ss', $a, $b);
$a = "$var1";
$b = "$var2"; 
$stmt->execute();
$stmt->bind_result($c, $d, $e);
while($stmt->fetch()) {
$result = "$d";
}
$stmt->close();
}

Ok now i want to make exactly same thing as before:
I mean:

if (!$result || mysql_num_rows($result) < 1){
$feedback = 'wrong entry, try again';
return $feedback;
} else {
if (mysql_result($result, 0, 'is_confirmed') == '1') {
user_set_tokens($user);
return 1;
} else {
$feedback = 'This account is not validated';
return $feedback;}

But i don't know how .. When i try this with prepared statement, i got error messages.
Please assist .. Thanks

[eelixduppy]

>> i got error messages.

This is the first step in solving your problem -- learning how to read error messages. Can you please list the errors you are receiving.

[bethesda]

Well i have info that "query is empty".
Of course it is empty, yet there is nothing added.

$result = mysql_query($query);
if (!$result || mysql_num_rows($result) < 1){
$feedback = 'wrong entry, try again';
return $feedback;
} else {
if (mysql_result($result, 0, 'is_confirmed') == '1') {
user_set_tokens($user);
return 1;
} else {
$feedback = 'This account is not validated';
return $feedback;}

There is no ($result) in my statement so i must changed it.
Since prepared statement uses a slightly different syntax - I wanted to ask how I should write it correctly?

[eelixduppy]

Perhaps something along the lines of this:

$form_user = strtolower($_POST['user']); 
$form_pass = $_POST['pass']; 

$db = new mysqli('localhost', 'username', 'password', 'database'); 
$stmt = $db->stmt_init(); 
if($stmt->prepare("SELECT `user`, `confirmed`, `adm` FROM `usr` WHERE `user` = ? AND pass = MD5(?)")) { 
$stmt->bind_param('ss', $form_user, $form_pass); 
$stmt->execute(); 
$stmt->bind_result($user, $confirmed, $adm); 
if($stmt->fetch()) { 
    if($confirmed == '1') { 
        user_set_tokens($user); 
        return 1; 
    } else { 
        return 'This account is not validated'; 
    } 
} else { 
    return 'wrong entry, try again'; 
} 
$stmt->close(); 
$db->close(); 

[bethesda]

Stayed with a few small changes, works excellent.
Thank you.

[bethesda]

So (fetch) is the same as (mysql_num_rows)?
What specifically contains a variable ($ stmt)?

Normally (mysql_query ($ query)) send result to a variable ($ query).
Division and numbering of the results was performed by (mysql_num_rows).

How does it look in prepared statements?

[eelixduppy]

If you read the documentation on each of these you'll be able to see how they operate.

For example, fetch [php.net] returns FALSE or NULL if there either was a problem or there is no data to return. Checking for this in an if statement allows you to know whether data is returned.

execute() is responsible to sending the query to the dbms. The equivalent method for mysql_num_rows is num_rows [php.net], however, you'll also need to store the results from the query in a buffer before calling this.

[bethesda]

Thanks.

[bethesda]

One more question..
When i pull out data from database i use in my scripts:

mysql_query('SET CHARACTER SET latin2');
mysql_query('SET collation_connection = latin2_general_ci');

why ? --> (¦Æ¬¦£Ó¡Ê)

Ok. Now when i use preapred_statements how can use SET CHARACTER SET ?

[bethesda]

ok this solve my problem:

$db -> query("SET NAMES 'latin2'");