Form SQL Injection Question

PHP
1

Hello,

I was wondering if the following scripts would be safe from an SQL injection or other attacks.

These are just a basic test script that I will be working into my main code, but am afraid my lack of knowledge on how to avoid SQL injections may leave me vulnerable to attack.

Is using prepared statements and binding the parameters sufficient enough or should I take further actions to protect myself?

Any comments would be appreciated.

Thank you,
Craig

Input Form:

<form action="testmessageoutput.php" method="POST">
<input type="hidden" id="testmessage_id" name="testmessage_id" value="2">
<textarea rows="20" cols="40" wrap="physical" name="manager_message">
Enter Test Text Here!!
</textarea>

<input type=submit
value="Submit Test Message">
</form>

PHP Processing Page testmessageoutput.php:


$manager_message =$_POST['manager_message'];
$testmessage_id=$_POST['testmessage_id'];

////////////////Check To See If Already Submitted/////////////////
try
{
$sql = "SELECT COUNT(*) AS num_row FROM man_message
WHERE testmessage_id = :testmessage_id";
$stmt = $db->prepare($sql);
$stmt->bindParam(':testmessage_id', $testmessage_id);
$stmt->execute();
$submitcheck = $stmt->fetch(PDO::FETCH_ASSOC);
}
catch(Exception $e)
{
echo $e->getMessage();
}      
//////////////If Submitted, Update Entry///////////////
if ($submitcheck['num_row'] > 0)
{
try
{
$sql = "UPDATE man_message 
SET manager_message = :manager_message WHERE testmessage_id = :testmessage_id";
$stmt = $db->prepare($sql);
$stmt->bindParam(':manager_message', $manager_message);
$stmt->bindParam(':testmessage_id', $testmessage_id);
$stmt->execute();
}
catch(Exception $e)
{
echo $e->getMessage();
}
}  
////////////////If Not, Insert Into Table////////////////                                 
else
{
try
{
$sql = "INSERT INTO man_message
(manager_message, testmessage_id)
VALUES (:manager_message, :testmessage_id)";
$stmt = $db->prepare($sql);
$stmt->bindParam(':manager_message', $manager_message);
$stmt->bindParam(':testmessage_id', $testmessage_id);
$stmt->execute();
}
catch(Exception $e)
{
echo $e->getMessage();
}
}
/////////Pull Value From Table and Display/////////////////////
try
{
$sql = "SELECT * FROM man_message WHERE testmessage_id = :testmessage_id";
$stmt = $db->prepare($sql);
$stmt->bindParam(':testmessage_id', $testmessage_id);
$stmt->execute();
while ($result = $stmt->fetch(PDO::FETCH_ASSOC)){
$display_message = $result['manager_message'];
}
}
catch(Exception $e)
{
echo $e->getMessage();
}
echo nl2br($display_message);
?>
2

I’m still very new at this myself, but shouldnt you be sanitaising the user inputs for ‘manager message’ and ‘testmessage’? By using somethign like mysqli_real_escape_string().

3

You’re using prepared statements, so you should be OK - however, I’d still validate the incoming data.

4

Would using quickform to build and validate the form do the trick?

5

You can filter one of the incoming vars quite simply:


if( (int)$_POST['testmessage_id'] === 0 ){

// basic failure to send an numeric id 
// fail early - because this should not happen at all

}

// otherwise go on and be assured at least the id has been filtered

If your testmessage_id fails here, then that is likely someone fiddling with your hidden fields and should be a red flag to you.

You could take similar filtering action with the message if, say, you stipulate very clearly what you do accept in your textarea.

Once those 2 filters are sorted out on the backend, then you could do similar client-side using JS to make the experience altogether more agreeable for the user if they were to veer from your on-screen instructions.

The above filtering example causing the testmessage_id to be turned into an integer is a very simple way of doing it, you should perhaps familiarise yourself with PHP 5.2.0 and onwards’ Filtering functions.