PDO prepared statement - what are colons in parameter names used for?

Question

I've seen many articles using colons (:) in front of named parameters when using PDO, and a couple that do not use the colon. I'd just as soon not use the colon, simply because it's one less keystroke and slightly easier to read.

It seems to be working fine for me, but I'm curious if there is something important that I'm missing when it comes to the use of colons?

For example, this works just fine:

function insertRecord ($conn, $column1, $comumn2) {
    try {
        $insertRecord = $conn->prepare('INSERT INTO Table1 (column1, column2)
        VALUES(:column1, :column2)');
        $insertRecord->execute(array(
                'column1' => $column1,
                'column2' => $column2
            ));
    }
    catch(PDOException $e) {
        echo $e->getMessage();
    }
}

As opposed to most developers using this, which also works:

function insertRecord ($conn, $column1, $comumn2) {
    try {
        $insertRecord = $conn->prepare('INSERT INTO Table1 (column1, column2)
        VALUES(:column1, :column2)');
        $insertRecord->execute(array(
                ':column1' => $column1,
                ':column2' => $column2
            ));
    }
    catch(PDOException $e) {
        echo $e->getMessage();
    }
}

Notice the colons in the execute statement parameters.

I'd like to understand what the colons are for.

Answer 1

TL;DR No, you are not missing anything. You must use colons (:) with named placeholders in the SQL string, but they are not required when executing the statement or binding parameters. PHP will infer a : if you leave it off in that context (see the second section below for an explanation and proof from the source code for the PHP interpreter itself).

What Works (What You Can Do in PHP)

In other words, this is acceptable:

$insertRecord = $conn->prepare('INSERT INTO Table1 (column1, column2)
    VALUES(:column1, :column2)');
//         ^         ^  note the colons

but this is not, because the placeholder names are ambiguous and look like column (or other) names:

$insertRecord = $conn->prepare('INSERT INTO Table1 (column1, column2)
    VALUES(column1, column2)');
//         ^        ^  no colons

By contrast, the colons are optional when using PDOStatement::bindParam() or PDOStatement::execute(). Both of these work basically identically:^*

$insertRecord->execute(array(
    ':column1' => $column1,
    ':column2' => $column2
));
// or
$insertRecord->execute(array(
    'column1' => $column1,
    'column2' => $column2
));

Why It Works (Exploring the PHP Source Code)

Why does it work this way? Well, for that we have to get into the c-language source code for PHP itself. To keep things current, I used the latest source from github (PHP 7), but the same basic analysis applies to earlier versions.

The PHP language expects named placeholders to have a colon in the SQL, as stated in the docs. And the documentation for PDOStatement::bindParam() indicates the parameter must be of the form :name when you bind the parameter to the placeholder. But that's not really true, for the reasons that follow.

There's no risk of ambiguity when it comes time to bind parameters or execute the statement because the SQL placeholder must have one and only one colon. This means the PHP interpreter can make a crucial assumption and do so safely. If you look at pdo_sql_parser.c in the PHP source code, particularly at line 90, you can see the valid list of characters in a placeholder, namely, alphanumerics (digits and letters), underscores, and the colon. Following the logic of the code in that file is a little tricky and hard to explain here—I'm sad to say it involves a lot of goto statements—but the short version is that only the first character can be a colon.

Put simply, :name is a valid placeholder in the SQL, but name and ::name are not.

This means that the parser can safely assume by the time you get to bindParam() or execute() that a parameter named name should really be :name. That is, it could just add a : before the rest of the parameter name. In fact, that's exactly what it does, in pdo_stmt.c, starting at line 362:

if (param->name) {
    if (is_param && param->name[0] != ':') {
        char *temp = emalloc(++param->namelen + 1);
        temp[0] = ':';
        memmove(temp+1, param->name, param->namelen);
        param->name = temp;
    } else {
        param->name = estrndup(param->name, param->namelen);
    }
}

What this does is, in slightly-simplified pseudocode:

if the parameter has a name then
    if the parameter name does not start with ':' then
        allocate a new string, 1 character larger than the current name
        add ':' at the start of that string
        copy over the rest of the name to the new string
        replace the old string with the new string
    else
        call estrndup, which basically just copies the string as-is (see https://github.com/php/php-src/blob/1c295d4a9ac78fcc2f77d6695987598bb7abcb83/Zend/zend_alloc.h#L173)

So, name (in the context of bindParam() or execute()) becomes :name, which matches our SQL, and PDO is perfectly happy.

Best Practices

Technically, either way works, so you could say it's a preference issue. But in case it's not obvious, this is not well-documented. I had to go on a very deep dive into the source code to figure this out, and it could theoretically change at any time. For consistency, readability, and easier searching in your IDE, use the colon.

---

^{* I say they work "basically" identically because the c code above imposes an extremely small penalty for leaving off the colon. It has to allocate more memory, build a new string, and replace the old string. That said, that penalty is in the nanosecond range for a name like :name. It might become measurable if you are prone to giving your parameters very long (like 64 Kb) names and you have a lot of them, in which case you have other problems... Probably none of this matters, anyway, as the colon adds an extremely small penalty in time to read and parse the file, so these two super-tiny penalties might even offset. If you're worried about performance at this level, you have much cooler problems keeping you awake at night than the rest of us. Also, at that point, you should probably be building your webapp in pure assembler.</sarcasm>}

Answer 2

Colons are required in the SQL statement, to indicate which identifiers are placeholders.

Colons in the execute() or bindParam() calls are optional. The documentation specifies them, but the implementation is clever enough to figure out what you mean if you leave them out (what else could you mean?).