[MySQLi] How to secure this?

[Sledmore]

Hey,

So I'm having ago at doing a little CMS for some hotel I'm planning on, I'm keepong OOP to the minimum, but using MySQLi but I'm having trouble on how to secure, I've read up quite a bit but unsure still.

This is my code I'm wanting to secure;

$CorpID = $_GET['id'];
$getCorpInfo = $mysqli->query("SELECT * FROM `jobs_employers` WHERE `id` = '".$CorpID."'") or die($mysqli->error);
if($getCorpInfo->num_rows == 0)
{
header("Location: index.php");
exit;
}
else
{
$corpInfo = $getCorpInfo->fetch_object();
$getOwnerInfo = $mysqli->query("SELECT * FROM `characters` WHERE `id` = '".$corpInfo->owner."'") or die($mysqli->error);
$ownerInfo = $getOwnerInfo->fetch_object();
}

I've been looking at the bind_param, but I'm sure I was doing it wrong, and on the PHP.NET website it says no longer to use that, but to use 'stmt_bind_param'. All help will be appreciated .

- Cheers.

 

[Beast]

Glad to see what seems like a rp cms, hopefully you can figure this out, best of luck. ;D

 

[Heaplink]

What you wan't to use is prepared statements (stmt_bind_param). So you can bind the data that you wan't to send. What they mean with using stmt_bind_param is prepare the query and then bind the parameters, set them and whatever you wan't. In example:

// Create prepared statement
if ($stmt = $mysqli->prepare("INSERT INTO `users` (`username`, `password`) VALUES (?, ?)")) {
   
    // Bind parameters
    // s stands for string - the type of data we wan't to bind
    $stmt->bind_param('ss', $username, $password);
 
    // Set the parameters
    $username = "Spongebob";
    $password = md5_with_token("The password");
 
    // You can do whatever you wan't before executing the prepared statement
 
    // Execute the prepared statement
    $stmt->execute();
 
    // You can echo the results after execute them
    echo "{$username} inserted with password {$password}.";
 
    // Set the parameters to something else
    $username = "Patrick";
    $password = md5_with_token("Another password");
 
    // Execute second query
    $stmt->execute();
 
    echo "{$username} inserted with password {$password}.";
 
    // Close statement
    $stmt->close();
} else {
    echo $mysqli->error;
}

You can also do the prepared statements outside of an if statement by just setting the variable to what we did above and using try catch instead - this will make sure that you can handle the exceptions as you wan't.

 

[Sledmore]

What you wan't to use is prepared statements (stmt_bind_param). So you can bind the data that you wan't to send. What they mean with using stmt_bind_param is prepare the query and then bind the parameters, set them and whatever you wan't. In example:

// Create prepared statement
if ($stmt = $mysqli->prepare("INSERT INTO `users` (`username`, `password`) VALUES (?, ?)")) {
 
    // Bind parameters
    // s stands for string - the type of data we wan't to bind
    $stmt->bind_param('ss', $username, $password);
 
    // Set the parameters
    $username = "Spongebob";
    $password = md5_with_token("The password");
 
    // You can do whatever you wan't before executing the prepared statement
 
    // Execute the prepared statement
    $stmt->execute();
 
    // You can echo the results after execute them
    echo "{$username} inserted with password {$password}.";
 
    // Set the parameters to something else
    $username = "Patrick";
    $password = md5_with_token("Another password");
 
    // Execute second query
    $stmt->execute();
 
    echo "{$username} inserted with password {$password}.";
 
    // Close statement
    $stmt->close();
} else {
    echo $mysqli->error;
}

You can also do the prepared statements outside of an if statement by just setting the variable to what we did above and using try catch instead - this will make sure that you can handle the exceptions as you wan't.

Thanks for the reply, I've just had another look around and found something that is seeming to work, I'm not sure if I should actually be doing it like this though, seems a bit much, but this is what I've just tried;

$stmt = $mysqli->prepare("SELECT `id` FROM `jobs_employers` WHERE `id` = ?");
$stmt->bind_param('i', $_GET['id']);
$stmt->execute();
$stmt->bind_result($CorpID);
$stmt->fetch();
$stmt->close();

However, it works perfectly fine.

 

[Heaplink]

The problem doing that, is that you can't catch the exceptions/errors (e.g. if the prepared statement couldn't be executed) therefore use either if else statement or try catch (with the above code).