views:

103

answers:

3

Positional parameters become a nightmare when dealing with more than 3 or 4 parameters. Named parameters are verbose. I'm thinking of doing this:

query("SELECT * FROM users WHERE username = ", $username, " AND password = ", $password)

With dynamic parameters (using func_get_args()), every second one being transformed into a positional parameter.

I've never seen this before and wanted to know if anyone has done this before and why/why not?

+2  A: 

It's a clever idea. The only problem I see is how to distinguish between SQL and passed-in variables. Unless you make an assumption that every second arg is a variable. I just think that assumption is fragile, and obfuscates things more than makes them clear.

Better way would probably be to use interpolation:

query("SELECT foo FROM bar WHERE id = #{id}",  array("id" => "23"));

Then write logic to interpolate these.

hakunin
Interpolation comes back to named attributes. You can already achieve it passing a second param to the execute() function.
Savageman
The only way I see to do original is to have class SQLParameter wrapping anything that has to be escaped. query("SELECT * FROM users WHERE username = ", new SQLParameter($username), " AND ..."); # etcBut this isn't making it much less verbose. PHP has no good syntax to make this simpler.
hakunin
OK maybe my example was a little misleading, I'm talking about big SQL statements like imagine if you had to do that with 10 parameters: with named parameters/interpolation as above you'd end up writing a big array literal and positional parameters would be horrendously confusing.
Ramon
I do like your thinking though, thanks.
Ramon
Whatever method you use, after 10 parameters things start to look a bit messy, even with direct SQL.
DisgruntledGoat
+1  A: 

Named parameters don't have to be verbose, at least not compared to positional parameters. You could use shortened versions that are still obvious:

$st = $dbh->prepare('SELECT * FROM users WHERE username = :u AND password = :p');
$st->bindValue(':u', $username);
$st->bindValue(':p', $password);
$st->execute();
DisgruntledGoat
+1  A: 

I don't think positional parameters are so bad... this is my favorite method:

function mysql_safe_string($value) {
 if(is_numeric($value))      return $value;
 elseif(empty($value))       return 'NULL';
 elseif(is_string($value))   return "'".mysql_real_escape_string($value)."'";
 elseif(is_array($value))    return implode(',',array_map('mysql_safe_string',$value));
}

function mysql_safe_query($format) {
 $args = array_slice(func_get_args(),1);
 $args = array_map('mysql_safe_string',$args);
 $query = vsprintf($format,$args);
 $result = mysql_query($query);
 if($result === false) echo '<div class="mysql-error"><strong>Error: </strong>',mysql_error(),'<br/><strong>Query: </strong>',$query,'</div>';
 return $result;
}

// example
$result = mysql_safe_query('SELECT * FROM users WHERE username=%s', $username);
Mark
Those aren't positional parameters in a prepared query. That's how you fake it when you don't have prepared queries.
outis
Oh. I thought 'positional' meant like the first `%s` refers to the first argument, and so forth...
Mark
You could make the case that you're using positional parameters. The main point of my comment is that you're not using prepared queries, thus not addressing OP's question.
outis