Develop Secure PHP Applications with PDO Statements

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions. Note that you cannot perform any database functions using the PDO extension by itself; you must use a database-specific PDO driver to access a database server.

PDO provides a data-access abstraction layer, which means that, regardless of which database you’re using, you use the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn’t rewrite SQL or emulate missing features. You should use a full-blown abstraction layer if you need that facility.

Database Connectivity –

$conn = new PDO(‘mysql:dbname=Database;host=127.0.0.1;charset=utf8’, ‘username’, ‘password’);

This line of code creates a new PDO object and saves it into the variable “$conn“.

The constructor takes 3 arguments: The DSN connection string, username and password.

In your development environment, you should also use this line:

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

This will throw an exception when an error occurs, you should use it for debugging, make sure to remove this line or change it to this:

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);

Prepared Statements –

Prepared statements will actually help you to keep your queries secure, as you don’t have to use something like mysql’s “mysql_real_escape_string” on every user-input anymore.

That’s the main reason why you should use PDO or mysqli over mysql.

1. Select from database – (Without Prepared Statements)

$stmt= $conn->query(“SELECT id FROM users WHERE role = 1”);
$fetch= $stmt->fetchAll();

foreach($fetch as $row)
{
echo “The user with the ID” . $row[‘id’] . ” is an admin.”;
}

When using the query() method, you automatically run the query. The results are saved in $stmt, but to make it accessible for us, we have to specify how we want the results to be returned.

fetchAll() returns an array containing all results.

2. Select from database – (With Prepared Statements)

$selectUser = $conn->prepare(“SELECT id FROM users WHERE name = :name LIMIT 1”);
$selectUser->bindParam(“:name”, $_POST[‘name’]);
$selectUser->execute();
$selectedUid = $selectUser->fetchColumn();

We prepare the query before we run it. The “:name” is like a placeholder for the name, we’ll tell PHP to ‘replace‘ it with a value with the bindParam method. That’s the simplest way to explain that. We run the query with the execute method.

fetchColumn is another way to return a result. However, it will only return one value per row.

3. Insert into database –

$query = $conn->prepare(“INSERT INTO users (name, email, password) VALUES (:name, :email, :password)”);
$query ->bindParam(“:name”, $_POST[‘username’]);
$query ->bindParam(“:email”, $_POST[’email’]);
$query ->bindParam(“:password”, $_POST[‘password’]);
if($query ->execute())
{
echo “User successfully registered!”;
}
else
{
echo “Something went wrong!”;
}

We create a query again and bind the parameters, quite easy as soon as you understood prepared statements, right? As you can see, the execute method returns a bool, it returns true when the query executes successfully and false if it fails somewhere.

4. Update a row –

$query= $conn->prepare(“UPDATE user SET email = :email WHERE id = :id”);
$query->bindParam(“:email”, $_POST[’emailaddress’]);
$query->bindParam(“:id”, $_SESSION[‘id’], PDO::PARAM_INT); ´
$query->execute();

As every query is basically the same (you type the query, use placeholders for your user input and bind it), we won’t go too much into detail in this query.

You probably wondered what the “PDO::PARAM_INT” argument does? It basically tells PDO to bind this parameter as an integer. That’s useful to use because it won’t add quotation marks like it’d do with strings.

PDO::PARAM_STRING is the default data type.

5. Delete a row –

$query = $conn->prepare(“DELETE FROM users WHERE id = :id”);
$query ->bindParam(“:id”, $_SESSION[‘id’], PDO::PARAM_INT); ´
$query ->execute();

There isn’t much to explain here anymore. You typed the query, binded the parameter and execute it. The user is now deleted.

Switching from mysql_* to PDO is easy, as you already know the MySQL syntax. If used correctly, it should save you lots of time as you don’t have to use mysql_real_escape_string or something similar anymore.

If you have any questions, feel free to contact us at yeahhub@gmail.com.

Post Views: 692

You may also like: