Safe and SIMPLE database inserts and other queries

Updated Isaac_4 4 498 Views

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.

<?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 for diafol
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

Edited by diafol
Member Avatar for Isaac_4
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!

Edited by Isaac_4
Member Avatar for diafol
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.

Member Avatar for veedeoo
veedeoo 474 Junior Poster

Thanks for sharing you codes.

Member Avatar for matrixdevuk
matrixdevuk 71 6t9.me Founder

You might also try this:

function db_query($sql, $params=array()) use ($pdo) {
Member Avatar for diafol
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 meeting, networking, learning, and sharing knowledge.

Edit Preview