11 Replies - 1383 Views - Last Post: 20 June 2011 - 09:55 AM Rate Topic: -----

#1 creativecoding  Icon User is offline

  • Hash != Encryption
  • member icon


Reputation: 922
  • View blog
  • Posts: 3,195
  • Joined: 19-January 10

Problem with prepared statements

Posted 19 June 2011 - 03:18 AM

I'm trying to make that switch to prepared statements now... Not that fun tbh.

Basically, I'm trying to login an admin. I usually do this by selecting a row with the same username/password combo and then checking if the num_rows returned is 1. I can't get it to work here...

Here's my code:
$mysqli = $this->mysqli();
		if($stmt = $mysqli->prepare("SELECT * FROM users WHERE username=? AND password=?")){
			$stmt->bind_param("ss", $username, $password);
			$stmt->execute();
			$stmt->bind_result($col1, $col2, $col3);
			if($stmt->num_rows == 1){
				$_SESSION['isAdmin'] = true;
				return true;
			} else {
				return false;
			}
			$stmt->close();
			$mysqli->close();
		} else {
			printf("Prepared statement error: %s \n", $mysqli->error);
		}



Yes I have tried it in phpMyAdmin, it worked. $col1, 2, and 3 return empty. Function is always returning false. Am I doing something wrong?

Is This A Good Question/Topic? 0
  • +

Replies To: Problem with prepared statements

#2 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 5954
  • View blog
  • Posts: 23,224
  • Joined: 23-August 08

Re: Problem with prepared statements

Posted 19 June 2011 - 04:20 AM

Are you implying, after all the time you've been here, that you're storing your passwords in the database as PLAINTEXT???

I hope, with this under your avatar:

Quote

Hash != Encryption

that's not the case!

In any event, where's the fetch of the results? You bind the result set, but you didn't actually fetch it.
Was This Post Helpful? 1
  • +
  • -

#3 creativecoding  Icon User is offline

  • Hash != Encryption
  • member icon


Reputation: 922
  • View blog
  • Posts: 3,195
  • Joined: 19-January 10

Re: Problem with prepared statements

Posted 19 June 2011 - 04:37 AM

View PostJackOfAllTrades, on 19 June 2011 - 05:20 AM, said:

Are you implying, after all the time you've been here, that you're storing your passwords in the database as PLAINTEXT???

I hope, with this under your avatar:

Quote

Hash != Encryption

that's not the case!


oh god no! NEVER! It's hashed on the outside, before the function is called. Securely with a salt and everything.


View PostJackOfAllTrades, on 19 June 2011 - 05:20 AM, said:

In any event, where's the fetch of the results? You bind the result set, but you didn't actually fetch it.


... fetch?

Just tried it, didn't work.


Updated code:
public function login($username, $password){
		$mysqli = $this->mysqli();
		if($stmt = $mysqli->prepare("SELECT * FROM users WHERE username=? AND password=?")){
			$stmt->bind_param("ss", $username, $password);
			$stmt->execute();
			$stmt->bind_result($col1, $col2, $col3);
			$stmt->fetch();
			if($stmt->num_rows == 1){
				$_SESSION['isAdmin'] = true;
				return true;
			} else {
				return false;
			}
			$stmt->close();
			$mysqli->close();
		} else {
			printf("Prepared statement error: %s \n", $mysqli->error);
		}
	}

Was This Post Helpful? 0
  • +
  • -

#4 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 5954
  • View blog
  • Posts: 23,224
  • Joined: 23-August 08

Re: Problem with prepared statements

Posted 19 June 2011 - 04:49 AM

OK, as far as the password, that's what I thought coming from you :)

I've not used mysqli and prepared statements, but based on the example in the docs, it looks like it would work if the query was successful.
Was This Post Helpful? 1
  • +
  • -

#5 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2834
  • View blog
  • Posts: 9,740
  • Joined: 08-August 08

Re: Problem with prepared statements

Posted 19 June 2011 - 05:56 AM

Where do you create the object?

$stmt = new mysqli("localhost", "my_user", "my_password", "world");


Was This Post Helpful? 1
  • +
  • -

#6 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2834
  • View blog
  • Posts: 9,740
  • Joined: 08-August 08

Re: Problem with prepared statements

Posted 19 June 2011 - 06:05 AM

Assuming you've created the object you might try outputting any connection error:
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}


as well as any errors when running the query:

if ($stmt->error) {
	echo $mysqli->error."<br />";
}


and the number of rows returned:
echo $stmt->num_rows."<br />";

Was This Post Helpful? 1
  • +
  • -

#7 creativecoding  Icon User is offline

  • Hash != Encryption
  • member icon


Reputation: 922
  • View blog
  • Posts: 3,195
  • Joined: 19-January 10

Re: Problem with prepared statements

Posted 19 June 2011 - 12:07 PM

I'm onto something.

I switched $stmt->fetch();

for

while($stmt->fetch(){
echo $col1 . $col2
echo $stmt->num_rows;
}



$col1 has the id, $col2 has the username, but the num_rows is STILL showing as 0. What's going on here?
Was This Post Helpful? 0
  • +
  • -

#8 creativecoding  Icon User is offline

  • Hash != Encryption
  • member icon


Reputation: 922
  • View blog
  • Posts: 3,195
  • Joined: 19-January 10

Re: Problem with prepared statements

Posted 19 June 2011 - 12:12 PM

mwuhahha, found it

I needed $stmt->store_info();. It stores info about your query and stuff or something like that.


Thanks guys!
Was This Post Helpful? 1
  • +
  • -

#9 creativecoding  Icon User is offline

  • Hash != Encryption
  • member icon


Reputation: 922
  • View blog
  • Posts: 3,195
  • Joined: 19-January 10

Re: Problem with prepared statements

Posted 19 June 2011 - 12:36 PM

While were on the topic, this doesn't work:

$mysqli = $session->mysqli();
		foreach($_POST as $key => $value){
			if (preg_match('/^s/', $key)) {
                if($stmt = $mysqli->prepare("UPDATE siteInfo SET `val`=? WHERE `key`=?")){
					$stmt->bind_param("ss", $value, $key);
					$stmt->execute() or die("There was an error.");
					$stmt->close();
				} else {
					die("MySQLi prepared statement failed:" . $mysqli->error);
				}
			}
		}
		$mysqli->close();


I'm saving everything that starts with an s into a database.

Everything works except for the update part. It doesn't update. The query works in phpMyAdmin but in the script, it does not work.
Was This Post Helpful? 0
  • +
  • -

#10 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3636
  • View blog
  • Posts: 5,759
  • Joined: 08-June 10

Re: Problem with prepared statements

Posted 19 June 2011 - 12:49 PM

View Postcreativecoding, on 19 June 2011 - 10:18 AM, said:

Basically, I'm trying to login an admin. I usually do this by selecting a row with the same username/password combo and then checking if the num_rows returned is 1. I can't get it to work here...

Why select data you're not going to be using? Don't neglect the COUNT() function ;)
$mysqli = $this->mysqli;
if ($stmt = $mysqli->prepare("SELECT COUNT(*) FROM users WHERE username=? AND password=?")) {
    $stmt->bind_param("ss", $username, $password);
    $stmt->execute();
    $stmt->bind_result($count);
    $stmt->fetch();
    if ((int)$count === 1) {
        $_SESSION['isAdmin'] = true;
        return true;
    } else {
        return false;
    }
    // You realize these will never execute, with the
    // return statements in the if/else clause above.
    $stmt->close();
    $mysqli->close();
} else {
    printf("Prepared statement error: %s \n", $mysqli->error);
}


And as a general rule, unless it's being using in some aggregated form, it's best to stay away from the * char. Unless you are 110% sure you actually need ALL the data. (Which is rare.)
Was This Post Helpful? 2
  • +
  • -

#11 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3636
  • View blog
  • Posts: 5,759
  • Joined: 08-June 10

Re: Problem with prepared statements

Posted 19 June 2011 - 12:56 PM

View Postcreativecoding, on 19 June 2011 - 07:36 PM, said:

Everything works except for the update part. It doesn't update. The query works in phpMyAdmin but in the script, it does not work.

No errors?

Successful UPDATE statements do not necessarily have to have any effect, even though they execute fine. I mean, if the WHERE clause excludes all the rows, the statement will execute without error but nothing is actually updated. To find out if this happens, check the mysqli_stmt::affected_rows value. If it's 0, the statement was fine but the key was simply not found.

Also, seeing as each key is only ever going to be updating a single row (right?) you may want to add a LIMIT clause to the update, just in case. It's hard to predict everything that goes wrong, but at least with this addition, no surprise can ever wipe out the entire table in one go.
Was This Post Helpful? 1
  • +
  • -

#12 creativecoding  Icon User is offline

  • Hash != Encryption
  • member icon


Reputation: 922
  • View blog
  • Posts: 3,195
  • Joined: 19-January 10

Re: Problem with prepared statements

Posted 20 June 2011 - 09:55 AM

Ah I got it. Problem with the keys! In the html form I added the prefix of s_ and forget to remove them.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1