Proper parameterised queries for database access

Aruna KN

Following code has not been written using proper parameterised queries for database access. I was unable to rewrite it as I'm not an experienced programmer. I am grateful, if anyone can rewrite following code using proper parameterised queries to compatible with PHP 8.1 with MariaDB 10.3

'.$j['date_added'].''.$j['message'].'

';
}
}

?>

Afzaal Ahmad Zeeshan

You're right. This code is not parameterized and would be prone to SQL injection attacks. However, the parameterization of the queries is not that difficult; easier than string concatenation. You escape the parameter areas and then provide the values for the parameters.

$query = $db->prepare('SELECT * FROM users WHERE uid = ?');
$query->execute([$userId]);

As you see, the string concatenation is removed and instead a simple escaped character is used. Try to rewrite the query, and if that doesn't work, we can help. :-) Read more: [PHP: Prepared Statements - Manual](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) [MySQLi: Bind in Execute - PHP 8.1 • PHP.Watch](https://php.watch/versions/8.1/mysqli\_stmt\_execute-params)

The shit I complain about It's like there ain't a cloud in the sky and it's raining out - Eminem ~! Firewall !~

Aruna KN

After your supportive reply, I rewrote the code after studying the Manual, but it gives an error_log:

[12-Apr-2023 04:15:21 UTC] PHP Fatal error: Uncaught TypeError: mysqli::prepare(): Argument #1 ($query) must be of type string, mysqli_stmt given in /home/student/public_html/friendsphp8/message.php:8
Stack trace:
#0 /home/student/public_html/friendsphp8/message.php(8): mysqli->prepare(Object(mysqli_stmt))
#1 {main}
thrown in /home/student/public_html/friendsphp8/message.php on line 8

My code is as follows: Please suggest me corrections :)

prepare("SELECT * FROM ".C_MYSQL_MESSAGES." WHERE (sender_id =? AND receiver_id=?) OR (sender_id =? AND receiver_id=?) ORDER BY `date_added` ASC");
$stmt = $conn->prepare($query);
$stmt->bind_param("ssss", $_SESSION['m'],(int)$_GET['id'],(int)$_GET['id'],$_SESSION['m']);
$stmt->execute();
$result = $stmt->get_result();

	     while($j = mysqli\_fetch\_array($result))
		{
			$c = mysqli\_query($conn,"UPDATE ".C\_MYSQL\_MESSAGES." SET status=1 WHERE id=".$j\['id'\]);
			
			$class = "";
			$date\_class = "";
			if($j\['sender\_id'\] == $\_SESSION\['m'\])
			{
				$class = "right";
				$date\_class = "date\_left";
			}
			else
			{
				$class = "left";
				$date\_class = "date\_right";
			}
				
			echo '

'.$j['date_added'].''.$j['message'].'

';
}
}

?>

OLD CODE IS AS FOLLOWS:

'.$j['date_added'].''.$j['message'].'

';
}
}

?>

Lost User

The bind_param("ssss" statement declares all four parameter values to be strings ...

$stmt->bind_param("ssss", $_SESSION['m'],(int)$_GET['id'],(int)$_GET['id'],$_SESSION['m']);

... but you then cast the two id values to be integers so there is a mismatch. The parameter specification string should be "siis", meaning "string, integer, integer, string". See PHP: mysqli_stmt::bind_param - Manual[^].

Aruna KN

I changed ssss into siis, again it makes an error_log:

[12-Apr-2023 08:04:27 UTC] PHP Fatal error: Uncaught TypeError: mysqli::prepare(): Argument #1 ($query) must be of type string, mysqli_stmt given in /home/student/public_html/friendsphp8/message.php:6
Stack trace:
#0 /home/student/public_html/friendsphp8/message.php(6): mysqli->prepare(Object(mysqli_stmt))
#1 {main}
thrown in /home/student/public_html/friendsphp8/message.php on line 6

Lost User

The error message is clear, parameter number 1 is not a string. So you need to look more closely at exactly what type each of these items is. You cannot make guesses or assumptions when working in this environment, you must be accurate and stick to the rules. So when you get an error message, first look at what the line in question is trying to do and examine why it is failing.

Aruna KN

Finally I was able to solve the code. Here what I did, Old code line:

$query = $conn->prepare("SELECT * FROM ".C_MYSQL_MESSAGES." WHERE (sender_id =? AND receiver_id=?) OR (sender_id =? AND receiver_id=?) ORDER BY `date_added` ASC");

New code line:

$query = "SELECT * FROM ".C_MYSQL_MESSAGES." WHERE (sender_id =? AND receiver_id=?) OR (sender_id =? AND receiver_id=?) ORDER BY `date_added` ASC";

Old code line:

$stmt->bind_param("siis", $_SESSION['m'],(int)$_GET['id'],(int)$_GET['id'],$_SESSION['m']);

New code line:

$stmt->bind_param("siis",$_SESSION['m'],$_GET['id'],$_GET['id'],$_SESSION['m']);

Final Code:

prepare($query);
$stmt->bind_param("siis",$_SESSION['m'],$_GET['id'],$_GET['id'],$_SESSION['m']);
$stmt->execute();
$result = $stmt->get_result();

	     while($j = mysqli\_fetch\_array($result))
		{
			$c = mysqli\_query($conn,"UPDATE ".C\_MYSQL\_MESSAGES." SET status=1 WHERE id=".$j\['id'\]);
			
			$class = "";
			$date\_class = "";
			if($j\['sender\_id'\] == $\_SESSION\['m'\])
			{
				$class = "right";
				$date\_class = "date\_left";
			}
			else
			{
				$class = "left";
				$date\_class = "date\_right";
			}
				
			echo '

'.$j['date_added'].''.$j['message'].'

';
}
}

?>

Thanks for your valuable suggestions to fix this issue