Safe and SIMPLE database inserts and other queries

Isaac_4

Here is a simple way to insert into a database that isn't much harder than using string concatenation - which we all know is very dangerous due to SQL injection attacks.

Put the code snippet into database.php.

Now, in a script handling a form post, such as post_reply.php:

<?php

require_once("database.php");

// Get session and post data to insert - no need for mysql_real_escape_string or other escaping
// (Note: Passwords should still go through password_hash() or crypt() though!)
$member_id = $_SESSION['member_id'];
$response_to = isset($_POST['response_to']) ? $_POST['response_to'] : (isset($_GET['response_to']) ? $_GET['response_to'] : '');
$subject = isset($_POST['subject']) ? $_POST['subject'] : '';
$post = isset($_POST['post']) ? $_POST['post'] : '';


// Use the db_query function from database.php with a parameter array to safely embed values into 
// the query without string subsitution. I seem to remember parameter binding doesn't quite work
// properly without the reference operator (&) on each variable in this array but can't test at
// the moment.
$result = db_query('INSERT INTO posts(author, response_to, subject, post) VALUES(?, ?, ?, ?)',
                        array(&$member_id, &$response_to, &$subject, &$post));

Even better of course would be to use a framework such as Laravel but for simple scripts this is very safe to use.

442 Views
About the Author

Senior PHP and .NET Framework developer

<?php
    
    // Connect to the database. This requires the PDO and PDO_Mysql modules to be installed in PHP.
    $pdo = new PDO('mysql:host=localhost;dbname=database_name', 'username', 'password');
    
    // Helper function to prepare and execute a statement with parameters immediately
    function db_query($sql, $params=array()) {
        global $pdo;
        $query = $pdo->prepare($sql);
        if($query->execute($params)) {
            return $query->fetchAll();
        } else {
            return array();
        }
    }
Member Avatar
diafol

Use of global discouraged. Why not pass the pdo object as a parameter? You'd then be sure to use the right instance if you had more than one.

I usually use a similar udf:

function get_result($db,$sql,$params=NULL)
{
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

Where $params is NULL or an array - either indexed or keyed

Isaac_4 39 Junior Poster in Training

Good point! Maybe first two lines of database.php replaced with this new function would be a good idea:

function db_connect()
{
    // Connect to the database. This requires the PDO and PDO_Mysql modules to be installed in PHP.
    // TODO: Store this connection string in a config file instead of hardcoded
    return new PDO('mysql:host=localhost;dbname=database_name', 'username', 'password');
}

// Helper function to prepare and execute a statement with parameters immediately
function db_query($conn, $sql, $params=array()) {
    $query = $conn->prepare($sql);
    if($query->execute($params)) {
        return $query->fetchAll();
    } else {
        return array();
    }
}

Then usage looks like:

$conn = db_connect();
$result = db_query($conn, 'INSERT INTO posts(author, response_to, subject, post) VALUES(?, ?, ?, ?)', array(&$member_id, &$response_to, &$subject, &$post));

Otherwise a custom PDO could be made if the use case was more complex.

I usually use a framework (CodeIgniter has been my favorite but lately I've been using Mako and starting with Symfony2) -- but sometimes a simple script is better in my opinion. Hopefully some people get some miledge out of these!

Member Avatar
diafol

I used to use frameworks but I found myself spending more time on them than if was writing my own stuff - or more correctly adapting stuff I already had. Simple scripts rock IMO. Hopefully anybody can adopt your code and carry on with it.

veedeoo 474 Junior Poster Featured Poster

Thanks for sharing you codes.

matrixdevuk 71 6t9.me Founder

You might also try this:

function db_query($sql, $params=array()) use ($pdo) {
Member Avatar
diafol

The closure construct above will also work, however it does tie you to using the specific $pdo object as the PDO object that must be passed to the function. This places limits on your client code.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.