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
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

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.

Thanks for sharing you codes.

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.