Stefan van de Laarschot Stefan van de Laarschot - 5 years ago 172
PHP Question

PHP Select query how to do it in a safe way

How can i do this piece of code in a safe way due

sql
injections.

if tried to read the php manual of
mysqli->prepared
but not able to convert it since i'm new to
PHP
development

NOTE:
DAL::$conn is $msqli = new mysqli()

$objects = array();
if($id != null)
{
$sql = "select * from Pages where id = ".$id;
}
else
{
$sql = "select * from Pages";
}

$result = mysqli_query(DAL::$conn, $sql);

if (mysqli_num_rows($result) > 0) {
// output data of each row
$records = 0;

while($row = mysqli_fetch_assoc($result)) {
$records++;
$data = new Pages();
$data->id = $row["id"];
$data->title = $row['title'];
$data->content = $row["content"];
$objects[$records] = $data;
}
} else {
//No results
}


Please no downvotes i'i know its a nooby question im trying to learn something learn php development.

Thnx in advance.

Answer Source

Any query can be injected whether it's read or write, persistent or transient. Injections can be performed by ending one query and running a separate one (possible with mysqli), which renders the intended query irrelevant.

Any input to a query from an external source whether it is from users or even internal should be considered an argument to the query, and a parameter in the context of the query. Any parameter in a query needs to be parameterized. This leads to a properly parameterized query that you can create a prepared statement from and execute with arguments. For example:

SELECT col1 FROM t1 WHERE col2 = ?

? is a placeholder for a parameter. Using mysqli, you can create a prepared statement using prepare, bind a variable (argument) to a parameter using bind_param, and run the query with execute. You don't have to sanitize the argument at all (in fact it's detrimental to do so). mysqli does that for you. The full process would be:

$stmt = mysqli->prepare("SELECT col1 FROM t1 WHERE col2 = ?");
$stmt->bind_param("s", $col2_arg);
$stmt->execute();

There is also an important distinction between parameterized query and prepared statement. This statement, while prepared, is not parameterized and is thus vulnerable to injection:

$stmt = mysqli->prepare("INSERT INTO t1 VALUES ($_POST[user_input])");

To summarize:

  1. All Queries should be properly parameterized (unless they have no parameters)
  2. All arguments to a query should be treated as hostile as possible no matter their source
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download