With many IBMi developers new to PHP, SQL and the web environment its important to cover a common mistake people make. The mistake is concatenating a value from $_REQUEST, $_GET or $_POST with their SQL statement string. This opens up the possibility for a SQL injection which allows someone to retrieve other data, bypass certain logic by making the statement always true, or worse (dropping a table, altering data, anything you can do in SQL). Below is how you can use a prepared SQL statement to safely execute SQL.

<?php
$db2Connection = db2_connect('MYIBMI', 'USERNAME', 'password',array('i5_lib' => 'MYLIB'));
$sql = "CALL CALCULATE_BMI_RPG(?,?,?)";
$stmt = db2_prepare($db2Connection, $sql);
if ($stmt) {
$height = 6.5;
$weight = 200;
$bmi = 0;
db2_bind_param($stmt, 1, "height", DB2_PARAM_IN);
db2_bind_param($stmt, 2, "weight", DB2_PARAM_IN);
db2_bind_param($stmt, 3, "bmi", DB2_PARAM_OUT);
if (db2_execute($stmt)) {
while($row = db2_fetch_assoc($stmt))
{
echo "Your bmi is ". $bmi;
}
}
}
else {
//If the sql statement isn't valid lets see the error message – This should only display if your in your development environment.
echo "<b>Last SQL Statement Error Code:</b> ".db2_stmt_error()."<br>";
echo "<b>Last SQL Statement Error Message:</b> ".db2_stmt_errormsg()."<br>";
}
if($db2Connection===FALSE){
//If the sql connection failed lets see the error message – This should only display if your in your development environment.
echo "<b>Last Connection Error #:</b> ".db2_conn_error()."<br>";
echo "<b>Last Connection Error Message:</b> ".db2_conn_errormsg()."<br>";
}

view raw
SQLInjectionPrevention DB2_* with bind_param.php
hosted with ❤ by GitHub

<?php
//Connect to the DB2
$db2Connection = db2_connect('MYIBMI', 'USERNAME', 'password',array('i5_lib' => 'MYLIB'));
//Create the SQL statement. Note that we use a question mark (?) / placemark to denote where our parameters are to go
$sql = "SELECT * FROM MYTABLE WHERE ID = ?";
//Prepare the query
$stmt = db2_prepare($db2Connection, $sql);
//Verify that the query is valid
if ($stmt) {
//We set the 1st element (starts at 0) of the $params array to the value we want to use on the first question mark.
//In this case we are taking the $_REQUEST which will take data from GET or POST http messages and pull the value
//with the key "ID"
$params[0] = $_REQUEST['ID'];
//Pass in the SQL statement that we prepared and the parameters array we want to run against the SQL statement
$result = db2_execute($stmt, $params);
if ($result) {
//if we get a result lets pull the data into an associated array and assign it to the $row variable
while($row = db2_fetch_assoc($stmt))
{
//lets dump out the row to verify we retrieved data
var_dump($row);
}
}
}
else {
//If the sql statement isn't valid lets see the error message – This should only display if your in your development environment.
echo "<b>Last SQL Statement Error Code:</b> ".db2_stmt_error()."<br>";
echo "<b>Last SQL Statement Error Message:</b> ".db2_stmt_errormsg()."<br>";
}
if($db2Connection===FALSE){
//If the sql connection failed lets see the error message – This should only display if your in your development environment.
echo "<b>Last Connection Error #:</b> ".db2_conn_error()."<br>";
echo "<b>Last Connection Error Message:</b> ".db2_conn_errormsg()."<br>";
}

view raw
SQLInjectionPrevention DB2_*.php
hosted with ❤ by GitHub

<?php
//Here's another example if you're using PDO and want to use bindValue and named placed holders instead of questions marks ?
$pdoConnection = new PDO("odbc:MYIBMI", "USER", "PASSWORD");
$stmt = $pdoConnection->prepare("SELECT * FROM MYTABLE WHERE ID = :ID");
$params['ID'] = $_REQUEST['ID'];
foreach ($params as $key => $value)
{
$stmt->bindValue(":".$key, $value);
}
$stmt->execute();

view raw
SQLInjectionPrevention PDO.php
hosted with ❤ by GitHub