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.

Edited by pritaeas: Changed to code snippet.

<?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();
        }
    }
4
Contributors
6
Replies
90
Views
3 Years
Discussion Span
Last Post by diafol
0

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

1

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

2

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.

0

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.

Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.