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>"; |
|
} |
|
<?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>"; |
|
} |
|
<?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(); |
Like this:
Like Loading...