Pdo Valid Characters For Placeholders

PDO valid characters for placeholders

The easiest way to find out, is to just check the source code:

BINDCHR     = [:][a-zA-Z0-9_]+;

You can use alphanumeric + underscore.

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

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?).

how to treat special characters as regular characters pdo

use quote from your pdo Connection Object

 $db->quote($search ); //Assuming your connection is $db

Though % is special in that, it is legal in a string, so you would probably have to escape it manually.

$search = str_replace("%", "\%", $search), 

pdo can't allow reuse of placeholders - what's an alternative for searching multiple columns?

You have to include the % signs in the parameters, not in the query

$testString =%testString%

Also using unnamed parameters ,? , requires a separate parameter for each ?. Using named parameters avoids this.

$stmt = $dbh->prepare("Select * from tableX where tableX.column1 LIKE :testString
                      OR tableX.column2 Like :testString OR ... ");
$stmt->bindParam(':testString',$testString, PDO::PARAM_STR);

What does the colon mean in :name when using php bindParam

That maps to the named placeholder in the query. It is not required for the binding, the driver will auto-added it if not present.

In your code you have

$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
                     ^^^^^^^^^              ^^^^^^^

The driver reads anything with the : and trailing text as a placeholder. It then swaps that content with the value being bound, escapes all special characters, and quotes the string.

So then your bindparam has

:calories and :colour which match up to each of those. Let's say $calories had o'brien. When the query went to the DB it would be:

SELECT name, colour, calories
FROM fruit
WHERE calories < 'o\'brien'

PDO also supports unnamed placeholders which are just question marks ?. You bind these by position.

$sth = $dbh->prepare('SELECT name, colour, calories
        FROM fruit
        WHERE calories < ? AND colour = ?');

and then use a 1 because it is the first placeholder.

$sth->bindParam(1, $calories, PDO::PARAM_INT);

Additionally you can just pass all values to the execute function as an array and it will do the binding as well.

Regardless of bindparam or execute binding you have to address the binding by how you use it in the query. Unnamed is positional, named is by name.

PDOException: SQLSTATE[HY093]

It might be worth trying a simplified version that omits the special characters from the PHP variable and the assigned placeholders.

<?php
    require_once('dbhandler.php');

    $dbh = new DBHandler();

    $e = 'svante@telia.com'; 
    $n = 'Svante';
    $a = 'Poffe';
    $l = '1596';

        $sql = "INSERT INTO users( `epost`, `namn`, `användarnamn`, `lösenord` ) VALUES( :e, :n, :a, :l )";

        $stmt = $dbh->getInstance()->prepare($sql);
        $stmt->bindParam(':e', $e, PDO::PARAM_STR);       
        $stmt->bindParam(':n', $n, PDO::PARAM_STR); 
        $stmt->bindParam(':a', $a, PDO::PARAM_STR);
        $stmt->bindParam(':l', $l, PDO::PARAM_STR);   

        $stmt->execute();

PDO (mysql) Invalid parameter number: parameter was not defined

Placeholders must be alphanumeric or underscore.

:esl_comments.commentSet is not a valid placeholder. Try just :commentSet instead.

(And of course the other ones will need to be replaced as well)

Pdo - insert values to db erorr SQLSTATE[HY093]

That error typically indicates that there is an error in the INSERT statement - the number of fields does not match the number of values to be inserted.

Something doesn't look right about the $filesize parameter used in the INSERT statement. Not sure that is proper syntax. Does $filesize have a value elsewhere in the code that's not displayed in your example?

---

Related Topics