Prepared Statements and Results In Arrays...?

  • (2 Pages)
  • +
  • 1
  • 2

26 Replies - 2042 Views - Last Post: 02 September 2014 - 06:44 AM Rate Topic: -----

#1 mb2000inc  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 24
  • View blog
  • Posts: 354
  • Joined: 10-November 09

Prepared Statements and Results In Arrays...?

Posted 28 August 2014 - 02:25 PM

Today is just not my DAY! I know why I need to use prepared statements, but OY! they are a pain in the backend.
So, the next problem I'm facing is working on a user authentication problem.

I'm using this function in a class that I created... But, i guess the problem is that prepared statements don't do arrays??
public function AuthenticateUser($userName, $password)
	{
		// username and password sent from form 
		$myusername = $userName; 
		$mypassword = $password; 
		
		// To protect MySQL injection (more detail about MySQL injection)
		$myusername = stripslashes($myusername);
		$mypassword = stripslashes($mypassword);
		$myusername = mysqli_real_escape_string($this->mysqli, $myusername);
		$mypassword = mysqli_real_escape_string($this->mysqli, $mypassword);
		
		$salt = $this->SelectSalt();//this is a function i have in another part of the code (its not stored in a table anywhere, I have a round about way of creating it that confuses everyone - for more security.)
		$prePass = hash('sha256', $salt.$mypassword);
		
		//prepared statement to select user info from user table
		if(!($stmt = $this->mysqli->prepare("SELECT * FROM UserTable WHERE UserName=?"))){
			echo "Prepare failed: (" . $this->mysqli->errno . ") " . $this->mysqli->error;
		}
		if (!$stmt->bind_param("s", $UserName)){
			return "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
		}
	
		if (!$stmt->execute()){
			return "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
		}
		else{
			$row = array();//this would be where I'm having troubles...
			stmt_bind_assoc($stmt, $row);
			while ($stmt->fetch()) {
				$user = $row['UserName'];
				$pass = $row['Password'];
				$role = $row['Role'];
				$uid = $row['UserID'];
				
				if($prePass = $pass)
				{
					//this is where I would be setting up sessions and stuff
				}
				else
				{
					//this is where I'd be logging failed login attemps
				}
			}
		}
		$stmt->close();
	}


So, how do I get those results out of this?
Any and all thoughts would be appreciated.

This post has been edited by mb2000inc: 28 August 2014 - 02:27 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Prepared Statements and Results In Arrays...?

#2 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3714
  • View blog
  • Posts: 13,467
  • Joined: 08-August 08

Re: Prepared Statements and Results In Arrays...?

Posted 28 August 2014 - 02:52 PM

First, why are you escaping the username and password? That's unnecessary with prepared statements.

Next, have you read this? http://php.net/manua...qli.prepare.php
Was This Post Helpful? 2
  • +
  • -

#3 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2330
  • View blog
  • Posts: 9,366
  • Joined: 03-December 12

Re: Prepared Statements and Results In Arrays...?

Posted 28 August 2014 - 03:02 PM

Anything can be a pain when at first you don't understand how it works and part of that would be using the array improperly. Assigning $row to the return value will automatically make it associative.
Was This Post Helpful? 1
  • +
  • -

#4 mb2000inc  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 24
  • View blog
  • Posts: 354
  • Joined: 10-November 09

Re: Prepared Statements and Results In Arrays...?

Posted 28 August 2014 - 03:21 PM

View PostCTphpnwb, on 28 August 2014 - 05:52 PM, said:

First, why are you escaping the username and password? That's unnecessary with prepared statements.

Next, have you read this? http://php.net/manua...qli.prepare.php


I didn't know that with prepared statements, you could do that. I just thought that it was a common practice.
And yeah, I did read that - but I honestly didn't understand some of it and tried to get it to work with how I was doing it above... it kept erroring out. I guess I need more practice. ;-)

********************

View Postastonecipher, on 28 August 2014 - 06:02 PM, said:

Anything can be a pain when at first you don't understand how it works and part of that would be using the array improperly. Assigning $row to the return value will automatically make it associative.


So, how would that work with this? Would I not use "stmt_bind_assoc($stmt, $row);"?

This post has been edited by mb2000inc: 28 August 2014 - 03:22 PM

Was This Post Helpful? 0
  • +
  • -

#5 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3714
  • View blog
  • Posts: 13,467
  • Joined: 08-August 08

Re: Prepared Statements and Results In Arrays...?

Posted 28 August 2014 - 03:32 PM

From the user comments:
while ($myrow = $result->fetch_assoc()) {

Was This Post Helpful? 2
  • +
  • -

#6 mb2000inc  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 24
  • View blog
  • Posts: 354
  • Joined: 10-November 09

Re: Prepared Statements and Results In Arrays...?

Posted 29 August 2014 - 06:31 AM

View PostCTphpnwb, on 28 August 2014 - 06:32 PM, said:

From the user comments:
while ($myrow = $result->fetch_assoc()) {


I replaced my
while ($stmt->fetch()) {

with yours
while ($myrow = $result->fetch_assoc()) {


though, I did substitute "$result" for "$stmt" (since I was using $stmt)

I got this error:

Quote

Fatal error: Call to undefined function stmt_bind_assoc() in /home/mysite/public_html/dev/classes/queries.php on line 156

Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is offline

  • the case is sol-ved
  • member icon

Reputation: 6374
  • View blog
  • Posts: 25,755
  • Joined: 12-December 12

Re: Prepared Statements and Results In Arrays...?

Posted 29 August 2014 - 06:48 AM

Yes, it is undefined because it doesn't exist, it is not a standard function.

If you have created this function yourself then you have failed to include it with your page.
Was This Post Helpful? 1
  • +
  • -

#8 mb2000inc  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 24
  • View blog
  • Posts: 354
  • Joined: 10-November 09

Re: Prepared Statements and Results In Arrays...?

Posted 29 August 2014 - 07:14 AM

View Postandrewsw, on 29 August 2014 - 09:48 AM, said:

Yes, it is undefined because it doesn't exist, it is not a standard function.

If you have created this function yourself then you have failed to include it with your page.


so, interestingly enough, I created it:
function stmt_bind_assoc (&$stmt, &$out) {
    $data = mysqli_stmt_result_metadata($stmt);
    $fields = array();
    $out = array();

    $fields[0] = $stmt;
    $count = 1;

    while($field = mysqli_fetch_field($data)) {
        $fields[$count] = &$out[$field->name];
        $count++;
    }    
    call_user_func_array(mysqli_stmt_bind_result, $fields);
}


but still get the same error...
Was This Post Helpful? 0
  • +
  • -

#9 andrewsw  Icon User is offline

  • the case is sol-ved
  • member icon

Reputation: 6374
  • View blog
  • Posts: 25,755
  • Joined: 12-December 12

Re: Prepared Statements and Results In Arrays...?

Posted 29 August 2014 - 07:24 AM

So it is still not finding it - you haven't included it successfully.

BTW it is not a good idea to name your functions too similar to the standard library ones.
Was This Post Helpful? 0
  • +
  • -

#10 mb2000inc  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 24
  • View blog
  • Posts: 354
  • Joined: 10-November 09

Re: Prepared Statements and Results In Arrays...?

Posted 29 August 2014 - 07:44 AM

View Postandrewsw, on 29 August 2014 - 10:24 AM, said:

So it is still not finding it - you haven't included it successfully.

BTW it is not a good idea to name your functions too similar to the standard library ones.


How do I include it successfully? I actually put it on the same page and made it a public function.
Was This Post Helpful? 0
  • +
  • -

#11 andrewsw  Icon User is offline

  • the case is sol-ved
  • member icon

Reputation: 6374
  • View blog
  • Posts: 25,755
  • Joined: 12-December 12

Re: Prepared Statements and Results In Arrays...?

Posted 29 August 2014 - 08:13 AM

Add the following to the top of your php so that you can see all errors during development:
error_reporting(E_ALL);
ini_set('display_errors', '1');

Was This Post Helpful? 1
  • +
  • -

#12 mb2000inc  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 24
  • View blog
  • Posts: 354
  • Joined: 10-November 09

Re: Prepared Statements and Results In Arrays...?

Posted 29 August 2014 - 08:31 AM

View Postandrewsw, on 29 August 2014 - 11:13 AM, said:

Add the following to the top of your php so that you can see all errors during development:
error_reporting(E_ALL);
ini_set('display_errors', '1');


I already have that in there (sort of)... I'm assuming that's why I get the error that I do...?

Posted Image

This post has been edited by mb2000inc: 29 August 2014 - 08:32 AM

Was This Post Helpful? 0
  • +
  • -

#13 andrewsw  Icon User is offline

  • the case is sol-ved
  • member icon

Reputation: 6374
  • View blog
  • Posts: 25,755
  • Joined: 12-December 12

Re: Prepared Statements and Results In Arrays...?

Posted 29 August 2014 - 08:45 AM

I think it should be '1' (or true) not 'On', On is a setting in a config file (I believe..).
Was This Post Helpful? 1
  • +
  • -

#14 ArtificialSoldier  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1828
  • View blog
  • Posts: 5,755
  • Joined: 15-January 14

Re: Prepared Statements and Results In Arrays...?

Posted 29 August 2014 - 09:24 AM

Quote

and made it a public function.

What does that mean, are you defining it as a method in a class? Global functions in PHP aren't public or private, they're just global functions.

More importantly, what't the purpose of the function?
function stmt_bind_assoc (&$stmt, &$out) {
    $data = mysqli_stmt_result_metadata($stmt);
    $fields = array();
    $out = array();

    $fields[0] = $stmt;
    $count = 1;

    while($field = mysqli_fetch_field($data)) {
        $fields[$count] = &$out[$field->name];
        $count++;
    }    
    call_user_func_array(mysqli_stmt_bind_result, $fields);
}


The way I read that, $fields is going to be an array of empty values because you set $out to an empty array, then try to read references to values from it. It doesn't have any values, you just set it to be an empty array.
Was This Post Helpful? 1
  • +
  • -

#15 mb2000inc  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 24
  • View blog
  • Posts: 354
  • Joined: 10-November 09

Re: Prepared Statements and Results In Arrays...?

Posted 29 August 2014 - 09:42 AM

View Postandrewsw, on 29 August 2014 - 11:45 AM, said:

I think it should be '1' (or true) not 'On', On is a setting in a config file (I believe..).

Ah, I see... The whole reason I had that on there was because I found it here, but no big. I will try that.

View PostArtificialSoldier, on 29 August 2014 - 12:24 PM, said:

Quote

and made it a public function.

What does that mean, are you defining it as a method in a class? Global functions in PHP aren't public or private, they're just global functions.

More importantly, what't the purpose of the function?
function stmt_bind_assoc (&$stmt, &$out) {
    $data = mysqli_stmt_result_metadata($stmt);
    $fields = array();
    $out = array();

    $fields[0] = $stmt;
    $count = 1;

    while($field = mysqli_fetch_field($data)) {
        $fields[$count] = &$out[$field->name];
        $count++;
    }    
    call_user_func_array(mysqli_stmt_bind_result, $fields);
}


The way I read that, $fields is going to be an array of empty values because you set $out to an empty array, then try to read references to values from it. It doesn't have any values, you just set it to be an empty array.



The purpose of the function was because of this issue:

View Postmb2000inc, on 29 August 2014 - 09:31 AM, said:

View PostCTphpnwb, on 28 August 2014 - 06:32 PM, said:

From the user comments:
while ($myrow = $result->fetch_assoc()) {


I replaced my
while ($stmt->fetch()) {

with yours
while ($myrow = $result->fetch_assoc()) {


though, I did substitute "$result" for "$stmt" (since I was using $stmt)

I got this error:

Quote

Fatal error: Call to undefined function stmt_bind_assoc() in /home/mysite/public_html/dev/classes/queries.php on line 156


And was told to create the function...

Also, after changing the error reporting to 1 rather than on - I still get the same message...
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2