Menu
I'm trying to learn more about MySQL and how to protect against SQL injections so my research has brought me to Prepared Statements which seems to be the way to go.
I'm also working on learning how to write Stored Procedures and am now trying to combine the two. There isn't much info on this though.
At the moment in my PHP test app I have a function that calls an SP with a normal MySQL command like this:
mysql_query("CALL usp_inserturl('$longurl', '$short_url', '$source')");
How can I do the same with MySQLi and a Prepared Statement to make it as safe as possible to injections?
Thanks!
867
We can use the prepared statements in a stored procedure by writing it inside the BEGIN and END block. We can understand it by creating an example that returns all records from a table by passing the table's name as a parameter of the stored procedure.
$stmt->bind_param("sss", $firstname, $lastname, $email); This function binds the parameters to the SQL query and tells the database what the parameters are. The "sss" argument lists the types of data that the parameters are. The s character tells mysql that the parameter is a string.
You must always use prepared statements for any SQL query that would contain a PHP variable. To do so, always follow the below steps: Create a correct SQL SELECT statement.
Try the following:
$mysqli= new mysqli(... info ...);
$query= "call YourSPWithParams(?,?,?)";
$stmt = $mysqli->prepare($query);
$x = 1; $y = 10; $z = 14;
$stmt->bind_param("iii", $x, $y, $z);
$stmt->execute();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
