Create a PHP Class for Toggling Between ENUM(0,1) Values

[justsomeguy]

To address your original error, note that prepare returns false if it fails:

http://php.net/manual/en/mysqli-stmt.prepare.php

The error message from MySQL does not get automatically printed by PHP.  If you're not checking for errors and error messages then you're going to end up in a staring contest with your code until the truth reveals itself.  Don't do that, just print the error message and figure out exactly why it's not working.

if (!$mysqli_stmt->prepare($sql_select)) {
  echo $mysqli_stmt->error;
}

The same is true for other methods like bind_param, with the mysqli extension you need to check for errors yourself and print the messages.

[iwato]

Quote

By the way, are the prepared statements also rejecting the forward slash.  For, I can eliminate the symbol from my hash generator.

 

Edited January 31, 2018 by iwato

[justsomeguy]

You should not have any data inside the query if you're using prepared statements at all.  That hash value should be passed to the prepared statement, not put inside the SQL.  And when you pass data to prepared statements it will always handle the data correctly, you don't need to sanitize anything.  The problems you're seeing with that are how PHP handles strings in general, it doesn't have anything to do with MySQL or SQL in general or prepared statements, it's PHP doing what it thinks you want to do with that string.

[iwato]

<?php
	class VeriFirm {
		private $mysqli_obj;

		private $username;
		private $email;
		private $hash;
		private $status;

		private $field;
		private $tbl_name = 'captive_roster';

		public $admin = 'admin@grammarcaptive.com';
		public $subject = 'Grammar%20Captive%20-%20Verify%20and%20Confirm%20User%20Action';
		
		public $msg_mismatch;
		public $msg_success; 
		public $msg_failure;

		public function __construct($mysqli_obj, $field) {
			$this->mysqli_obj = $mysqli_obj;
//			if(!empty($_GET['username']) AND !empty($_GET['email']) AND !empty($_GET['hash']) AND !empty($_GET['field'])){
			if(!empty($_GET['username']) AND !empty($_GET['email']) AND !empty($_GET['hash'])) {
				$this->username = $mysqli_obj->real_escape_string($_GET['username']);;
				$this->email = $mysqli_obj->real_escape_string($_GET['email']);
				$this->hash = $mysqli_obj->real_escape_string($_GET['hash']);
//				$this->field =  $mysqli_obj->real_escape_string($_GET['field']);
				$this->field =  $field;
			}			
		}

		public function create_link() {
			return $mailto = "mailto:" . $this->admin . "?Subject=" . $this->subject;			
		}

		public function update_status() {
			$mysqli_obj = $this->mysqli_obj;
			$mysqli_stmt = $mysqli_obj->stmt_init();
			$sql_select = "SELECT user_name, email_address, psw_hash, " . $this->field . " FROM " . $this->tbl_name . " WHERE user_name=? AND email_address=? AND psw_hash=?";
			$mysqli_stmt->prepare($sql_select);
			$mysqli_stmt->bind_param("sss", $this->username, $this->email, $this->hash);
			$mysqli_stmt->execute();
			$mysqli_result = $mysqli_stmt->get_result();
			$match = mysqli_num_rows($mysqli_result);
			if($match > 0){
				while ($row = $mysqli_result->fetch_assoc()) {
					foreach ($row as $key => $value) {
						$result[$key] = $value;
					}
				}
				$this->status = $result[$this->field];
				print_r($result); echo '<br />';
			if ($this->status == 0) {				
				$sql_update = "UPDATE " . $this->tbl_name . " SET " . $this->field . '="1" WHERE user_name=? AND email_address=? AND psw_hash=?';
				$mysqli_stmt->prepare($sql_update);
				$mysqli_stmt->bind_param("sss", $this->username, $this->email, $this->hash);
				var_dump($mysqli_stmt); echo '<hr>';
				if ($mysqli_stmt->execute()) {
					return $this->msg_success;
				} else {
					return $this->msg_failure;
				}
			} else if ($this->status == 1) {
				$sql_update = "UPDATE " . $this->tbl_name . " SET " . $this->field . '="0" WHERE user_name=? AND email_address=? AND psw_hash=?';
				$mysqli_stmt->prepare($sql_update);
				$mysqli_stmt->bind_param("sss", $this->username, $this->email, $this->hash);
				var_dump($mysqli_stmt); echo '<hr>';
				if ($mysqli_stmt->execute()) {
					return $this->msg_success;
				} else {
					return $this->msg_failure;
				}
			}
			} else {
				return $this->msg_mismatch;
			}
		}
		
		public function get_admin_and_subject() {
			return $this->admin . " and " . $this->subject;			
		}
		public function set_admin_and_subject($admin, $subject) {
			$this->admin = $admin;
			$this->subject = $subject;			
		}

		public function get_tablename() {
			return $this->tbl_name;			
		}
		public function set_tablename($tbl_name) {
			$this->tbl_name = $tbl_name;
		}

		public function get_field() {
			return $this->field;			
		}
		public function set_field($field) {
			$this->field = $field;
		}

		public function get_status() {
			return $this->status;			
		}

		public function get_msg_mismatch() {
			return $this->msg_mismatch;
		}
		public function set_msg_mismatch($msg_mismatch) {
			$this->msg_mismatch = $msg_mismatch;
		}

		public function get_msg_success() {
			return $this->msg_success;
		}
		public function set_msg_success($msg_success) {
			$this->msg_success = $msg_success;
		}

		public function get_msg_failure() {
			return $this->msg_failure;
		}
		public function set_msg_failure($msg_failure) {
			$this->msg_failure = $msg_failure;
		}
	}
?>

Please find above the tentative completed class.  It has been tested, and it works.  The most important structural rearrangement necessary to make it work required that I dissolve the  the update_record() function and transfer its content into the match_data() function that I have renamed as the update_status() function.  There were other changes required to get the UPDATE SET statement to work properly, but everything is running smoothly now. 

QUESTION ONE:  Are you suggesting that I remove the various implementations of the  real_escape_string( ) functions?

[Ingolme]

The real_escape_string() function is not needed for prepared statements, in fact it will probably add unnecessary backslashes which make the query not return the expected results.