Question mark placeholder

Question

How can I replace all '?' the variables? Something like:

   $name = 'test' ;
   $lname = 'lastTest';
   $id = 1 ;
   ->where ( 'customers.name = ? and customers.lastname = ? and customers.id = ?' , $name , $lname , $id ) ;

output:

customers.name = 'test' and customers.lastname = 'lastTest' and customers.id = 1

Any ideas?

why do you need that? are you implementing your own placeholder system for the database or what? Can't you be more specific? — Your Common Sense, Dec 11 '11 at 13:35
Couldn't you just pass it to PDO and let that handle prepared statements? (Undoing the syntax and reverting to SQL concatenation is kind of not a useful idea.) — mario, Dec 11 '11 at 13:37
Doing text replacement in the query string is **not** the way to implement placeholders. Use prepared statements with bind variables. — Mat, Dec 11 '11 at 13:38
Even if you don't always use PDO, the system you replace it with should be at least as secure as PDO or you're just inviting trouble... And if you're doing string concatenation/manipulation to generate your query, there *will* be holes. There always are. — Basic, Dec 11 '11 at 13:43
@Basiclife Well, if PDO doing string concatenation/manipulation by default - doesn't that mean that there *is* hole in PDO? — Your Common Sense, Dec 11 '11 at 13:48
@Col.Shrapnel Not at all - PDO *doesn't* do string manipulation, it sends the query and the parameters seperately to SQL - SQL is then responsible for getting an execution plan from the query/params - hence SQL Injection proof if used correctly — Basic, Dec 11 '11 at 14:13
@Col.Shrapnel http://stackoverflow.com/questions/4042843/in-php-how-does-pdo-protect-from-sql-injections-how-do-prepared-statements-wor — Basic, Dec 11 '11 at 14:58
@Basiclife thank you, I know how native prepared statements works. I am talking of PDO which doesn't use them by default. — Your Common Sense, Dec 11 '11 at 15:05
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/5752/discussion-between-basiclife-and-col-shrapnel) — Basic, Dec 11 '11 at 15:11

score 5 · Accepted Answer · answered Dec 11 '11 at 13:38

I really think you should use a library like PDO, but here is a solution nonetheless:

public function where($query)
{
    $args = func_get_args();
    array_shift($args);

    $query = preg_replace_callback('/\?/', function($match) use(&$args)
    {
        return array_shift($args); // wrap in quotes and sanitize
    }, $query);

    return $query;
}

Your Common Sense · Answer 2 · 2011-12-11T14:54:01.633

well, for such a primitive substitutions you can use standard printf syntax.

$name = "'test'";
$lname = "'lastTest'";
$id = 1;
$sql = sprintf('customers.name = %s and customers.lastname = %s and customers.id = %d' , 
                $name , $lname , $id ) ;

Note that printf syntax supports placeholder escaping for such a rare yet possible case when you will have to add a literal placeholder symbol into query.

However, for the real life usage I'd implement typed placeholders, letting you add different kind of data - identifiers, arrays and such

score 1 · Answer 3 · answered Dec 11 '11 at 13:49

// ModifiedQuery variable stores your expected format.

$query  = "customers.name = ? and customers.lastname = ? and customers.id = ?";
$params = array("?", "?", "?");
$actualParams   = array($name, $lname, $id);

$modifiedQuery = str_replace($params, $actualParams, $query);

score 0 · Answer 4 · answered Dec 11 '11 at 16:18

0

I don't know PDO to be honest, but prepared statements do this for you already with mysqli, would this be an option?

answered Dec 11 '11 at 16:18

Kelly Milligan

578
1
4
17

prepared statements has very limited usability – Your Common Sense Dec 11 '11 at 16:29
say, for the SET operator you have to write a small program of your own. and there are no identifier placeholder at all. and so on. – Your Common Sense Jan 03 '12 at 01:36

Question mark placeholder

4 Answers4

Linked