Solved

SELECT and then UPDATE prepared statement

Posted on 2016-09-24
9
23 Views
Last Modified: 2016-09-29
I have decided that after trying procedural and object oriented ways I want to stick with prepared statements if possible. I am having trouble trying to update though after using select. Basically, if the record exists, an update needs to happen to that particular record:

if(isset($_GET['activecode'])) {
	
	
	
	$stmt = $link->prepare("SELECT `user_hash` FROM `db_users` WHERE `user_hash` = ?");
	$stmt->bind_param("s", $activate_url);
	$activate_url = urlencode($_GET['activecode']);
	$stmt->execute();
	$result = $stmt->get_result();
	$numRows = $result->num_rows;
	if($numRows === 1) {
	
		
	$stmt = $link->prepare("UPDATE user_active SET user_active = ? WHERE `user_hash` = '$activate_url'");
	$stmt->bind_param("i", 1);
	$stmt->execute();
	$stmt->close();

		
	set_message("<p>Your account has been activated.</p>");
	
	
} 

Open in new window


I am getting the following error:

Fatal error: Uncaught Error: Call to a member function bind_param() on boolean in /Applications/MAMP/htdocs/mysite/activate_account.php:20 Stack trace: #0 {main} thrown on line 20

Line 20 is:

$stmt->bind_param("i", 1);

Open in new window

0
Comment
Question by:Black Sulfur
  • 5
  • 4
9 Comments
 
LVL 17

Expert Comment

by:bigeven2002
ID: 41813801
Hello,
From what I read online, the prepare() statement can return false so there should be a check for that with a conditional statement.  If it does indeed return false, then perhaps there is a typo in the Select or Where clause for the table or column names.

So assuming the error corresponds with line 14 in your code sample, I would start there.
0
 

Author Comment

by:Black Sulfur
ID: 41813824
Not sure if I am doing it right but I added an if statement to show me
var_dump($link->error);

Open in new window


The result is:

string(0) ""
0
 
LVL 17

Expert Comment

by:bigeven2002
ID: 41813834
OK, if i remember correctly, a blank result or 0 is the same as false.  I'm a bit rusty with these object oriented methods so please forgive my ignorance.

Curious, instead of using bind_param in the Update query, what if you statically enter the 1 in place of the ? in the SET clause?
$stmt = $link->prepare("UPDATE user_active SET user_active = 1 WHERE `user_hash` = '$activate_url'");
#$stmt->bind_param("i", 1); # commented out

Open in new window


Also, in the UPDATE statement, is user_active also the name of the Table?  Shouldn't it be db_users?
0
 

Author Comment

by:Black Sulfur
ID: 41813838
Ah, just realised my table name was wrong but even after changing i still get the same result:

if(isset($_GET['activecode'])) {
	
	
	
	$stmt = $link->prepare("SELECT `user_hash` FROM `db_users` WHERE `user_hash` = ?");
	$stmt->bind_param("s", $activate_url);
	$activate_url = urlencode($_GET['activecode']);
	$stmt->execute();
	$result = $stmt->get_result();
	$numRows = $result->num_rows;
	if($numRows === 1) {
	
		
	if($stmt = $link->prepare("UPDATE `db_users` SET `user_active` = ? WHERE `user_hash = '$activate_url'")) {
	$stmt->bind_param("i", 1);	
	$stmt->execute();
	$stmt->close();
	
	
	set_message("<p>Your account has been activated.</p>");
		
	} else {
		
		var_dump($link->error);
	}
	
} 
	
	else {
		
		header("location:index.php");
		
	} 

}

Open in new window

0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Black Sulfur
ID: 41813845
Thanks for the input bigeven2002.

I am not sure I want to comment out the bind_param as I think that is the part that prevents SQL injection (I'm no expert). Am I wrong in saying that?
0
 

Accepted Solution

by:
Black Sulfur earned 0 total points
ID: 41813850
I figured it out! It seems that you have to use a variable in the bind statement. I was just using 1.

This is what it looks like now and it seems to work:

if(isset($_GET['activecode'])) {
	
	
	
	$stmt = $link->prepare("SELECT `user_hash` FROM `db_users` WHERE `user_hash` = ?");
	$stmt->bind_param("s", $activate_url);
	$activate_url = urlencode($_GET['activecode']);
	$stmt->execute();
	$result = $stmt->get_result();
	$numRows = $result->num_rows;
	if($numRows === 1) {
	
		
	if($stmt = $link->prepare("UPDATE `db_users` SET `user_active` = ? WHERE `user_hash` = '$activate_url'")) {
	$stmt->bind_param("i", $user_active);	
	$user_active =1;
	$stmt->execute();
	$stmt->close();
		
	set_message("<p>Your account has been activated.</p>");
		
	} else {
		
		var_dump($link->error);
	}
	
} 
	
	else {
		
		header("location:index.php");
		
	} 

}

Open in new window

0
 
LVL 17

Expert Comment

by:bigeven2002
ID: 41813851
OK, no problem, let's try this, which will slightly rewrite the concatenation.

$stmt = $link->prepare("UPDATE `db_users` SET `user_active` = ? WHERE `user_hash = ".$activate_url));
$stmt->bind_param("i", 1);	

Open in new window


If that still fails, then type in the manual values and comment out the bind_param line like so.  Since these are static values, that should negate SQL injection, but this is just for testing anyways.

$stmt = $link->prepare("UPDATE `db_users` SET `user_active` = 1 WHERE `user_hash = '1234567890'));
# $stmt->bind_param("i", 1);	# commented entire line out with the # sign

Open in new window

0
 
LVL 17

Expert Comment

by:bigeven2002
ID: 41813853
OK great!  Glad you got it figured out.
0
 

Author Closing Comment

by:Black Sulfur
ID: 41821344
I figured out that I had to put 1 into a variable in order for the prepared statement to work.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now