Using Php To Modify Sql Query

[CORT0619]

Does anyone know of a way that I can use php to determine if a variable is included in a query? But the thing is that I don't think it could be a simple if statement because it's not just one variable I'm testing it's many. My problem is that I'm trying to check whether the variable has a value and if it does to then include it in the query but in the "WHERE" part of the sql query. But like I said any of the variables that have a value would also be included in the same query.

 

Thanks.

[requinix]

Do you have some examples?

[CORT0619]

Well an example of my current query is $query = "SELECT ticket#, date, name, phone, email, description, tech, notes FROM table WHERE name='$name' OR email='$email' OR phone='$phone' OR tech='$tech'";

 

But for instance if the administrator selects just the name and that's it, all of the tickets still come up because OR is used in the WHERE part of the query.

[CORT0619]

I was wondering if I can do an if statement within the sql query such as "if(empty($name) || $name == '' || $name == null) {}else{name='$name'}" however I'm not sure how to write it correctly, because the sql query is embedded within php.

[requinix]

Build the query up as a string. For example

$conditions = array();
if (!empty($name)) {
    $conditions = "`name` = '" . /* escape($name) */ . "'";
}
if (!empty($email)) {
    $conditions = "`email` = '" . /* escape($email) */ . "'";
}
// ...

$query = "SELECT fields FROM table";
if ($conditions) {
    $query .= " WHERE " . implode(" OR ", $conditions);
}

// execute $query

[Langstra]

You could also make a function queryBuilder

/**
* $select, array with elements you want to select, null for *
* $from, table you want to get information from
* $where, the array with values you want to check on
           WHERE key = value
*/

function queryBuilder($select,$from,$where) {
   $query = "SELECT";
   if($select == null) { //If there are no specific things to select, select all
       $query .= " *";
   } else {
       foreach($select as $s) {
           $query .= " ".$s.","; //add every select with a trailing ,
       }
       $query = str_split($query, strlen($query)-1); //remove the last ,
       $query = $query[0];
   }
   $query .= " FROM ".$from;
   if($where != null) {
       $query .= " WHERE";
       foreach($where as $key => $value) {
           $query .= " ".$key."=".$value." OR";
       }
       $query = str_split($query, strlen($query)-3); //remove the last OR
       $query = $query[0];
   } // else no where statement and thus end of query
   return $query;
}

Edited December 26, 2012 by Langstra

[Christian F.]

I wouldn't recommend using that function, I'm afraid. Not only is it poorly documented and rather inefficient, but it also requires you to do all escaping prior to building the query. Quite contrary to best practises, and since it obfuscates the query building process it'll just increase the likelihood of the developer forgetting about securing the data.

 

If you're going to use a query builder, then I recommend basing it upon the example provided by requinix.

[CORT0619]

Ok I will do that Christian. Requinix so where the code says /* escape($name) */ am I supposed to just put the variable name?

[Christian F.]

You're supposed to use the proper method of escaping it, and use the variable name there, yes. Since that's two string values, in the example, that would mean real_escape_string () on both of them. Had it been a integer value, then using (int) to explicitly cast it as such would be the correct choice.

[CORT0619]

Oh ok. Well there is only one field that isn't coming from a selection list.

[Christian F.]

All input coming from the client, whether it be GET, POST, cookie or whatnot must be validated and escaped. Otherwise you will be open for attacks, and they will be exploited.

[CORT0619]

Okay, I'm having a problem with the implode function. I'm getting the error message "Invalid arguments passed" and I set $conditions equal to a string to verify that it wasn't coming up as null so I'm not sure what the problem is. My code is:

 

 

if($conditions) {

 

$query .= " WHERE " . implode(" AND ", $conditions);

 

echo $query;

 

}

[Jessica]

Implode needs an array. You set the Argument you give it to a string.

...

[CORT0619]

No, I set up my code like requinix where the variable is set to array.

[requinix]

So "I set $conditions equal to a string" was a bit less than truthful?

 

Post your code.

[Christian F.]

For your info: requinix made a string as well in his example, even though he treated it as a array later on.

Always expect errors, omissions or other things that will cause problems if used directly, in the examples you're given on a forum. Even if the author says to use it as is. That'll save you a whole lot of headaches, and help you actually understand the problem and solution a lot better.

[Muddy_Funster]

ok, here's a little class I knocked up quick to show what it would look like to do something like this that way. The class would expect either a multi dimensional array or multiple key value pairs passed in to set the values. the conditions (the bit used after the WHERE) needs to be a multi dimesional array with field and value and flag set as keys for the arrays within it.

 

This is still a massive over simplification as it only works with select queries, only works against a single table, and only words with absolout value checking in the WHRE clause (use of = and nothing else)

 

This is untested and uncommented - it's really just to give you an idea of where you should be going with this. if anyone wants to play with it and make it work, do please be my guest.

class buildMySQLSelect(){
public $finalQuery;
public $cols;
public $table;
public $condition;

public function setQryVals($val, $stVal=null){
if(is_array($val)){
foreach($val as $key => $value){
$this->$key = $value;
}
}
elseif(($stVal != null) &&($val =='cols' || $val =='table' || $val == 'condition')){
$this->$val = $stVal;
}
}

public function create(){
$this->makeSafe();

if(!empty($this->cols) && !empty($this->table)){
if(is_array($cols)){
$this->cols = implode(', ', $this->cols);
}
$q_cols = "SELECT {$this->cols}";

$q_table = "FROM {$this->table}";

$q_cnd = "";
if(!empty($this->condition)){
$cndCount = count($this->condition);
if($cndCount == 1){
$q_cnd .= "WHERE {$this->condition['0']['field']} = {$this->condition['0']['value']}";
}
elseif($cndCount > 1)){
$q_cnd .= "WHERE";
for($i=1; $i<=$cndCount; $i++){
$pKey = $i - 1;
if($i == $cndCount){
$q_cnd .= " {$this->condition[$pKey]['field']} = {$this->condition[$pKey]['value']}";
}
else{
$q_cnd .= " {$this->condition[$pKey]['field']} = {$this->condition[$pKey]['value']} {$this->condition[$pKey]['flag']} ";
}
}
}
$this->finalQuery = "$q_cols $q_table $q_cnd";
}
}
}

private function makeSafe(){
if(is_array($this->cols)){
foreach ($this->cols as $idx => $value){
$value = "`".$value."`";
}
}
else{
$this->cols = "`".$this->cols."`";
}

$this->table = "`".$this->table."`";

foreach($this->condition as $pIDX=> $sArray){
$sArray['field'] = "`".$sArray['field']."`";
$sArray['value'] = trim($sArray['value']);
if(!filter_var($sArray['value'], FILTER_VALIDATE_INT) && !filter_var($sArray['value'], FILTER_VALIDATE_FLOAT)){
if(filter_var(filter_var($sArray['value'], FILTER_SANITIZE_EMAIL), FILTER_VALIDATE_EMAIL){
$sArray['value'] = filter_var($sArray['value'], FILTER_SANITIZE_EMAIL);
$sArray['value'] = mysql_real_escape_string($sArray['value']);
}
else{
$sArray['value'] = filter_var($sArray['value'], FILTER_SANIZE_STRING, FILTER_ENCODE_LOW);
$sArray['value'] = musql_real_escape_string($sArray['vlaue']);
}
}
if(!isset($sArray['flag']) || (trim($sArray['flag']) !="AND" && trim($sArray['flag']) != "and" && trim($sArray['flag']) !="OR" && trim($sArray['flag']) != "or")){
$sArray['flag'] = 'AND';
}
}
}
}

[Jessica]

I set $conditions equal to a string

 

[CORT0619]

Yes I did for testing purposes after the fact. but excuse me if I thought this forum was for helping ppl, u don't have to be nasty.

[requinix]

For your info: requinix made a string as well in his example, even though he treated it as a array later on.

Ah shoot, there was a typo: I missed the []s.

$conditions = array();
if (!empty($name)) {
    $conditions[] = "`name` = '" . /* escape($name) */ . "'";
}
if (!empty($email)) {
    $conditions[] = "`email` = '" . /* escape($email) */ . "'";
}
// ...

$query = "SELECT fields FROM table";
if ($conditions) {
    $query .= " WHERE " . implode(" OR ", $conditions);
}

// execute $query

In my defense it should have been really easy to spot that (I defined $conditions as an array right at the start) and fix it.

[Muddy_Funster]

Yes I did for testing purposes after the fact. but excuse me if I thought this forum was for helping ppl, u don't have to be nasty.

No one is being nasty, just pointing out the inconsistancy of your statements. It's not like people haven't taken the time to give you help here.

[Christian F.]

Requinix: Hehe, I reckoned it was a typo when I saw those missing angle brackets. As you said, quite obvious if you read through the code.

 

CORT0619: As for the nastiness your perceived, let me quote you from this excellent article:

Much of what looks like rudeness in hacker circles is not intended to give offense. Rather, it's the product of the direct, cut-through-the-bullshit communications style that is natural to people who are more concerned about solving problems than making others feel warm and fuzzy.

 

When you perceive rudeness, try to react calmly. If someone is really acting out, it is very likely a senior person on the list or newsgroup or forum will call him or her on it. If that doesn't happen and you lose your temper, it is likely that the person you lose it at was behaving within the hacker community's norms and you will be considered at fault. This will hurt your chances of getting the information or help you want.

 

In an effort to help as many people as we can, and still have time to do what we need to do otherwise, we tend to condense our communications to a bare minimum. We programmers (most of us at least) tend to favour actual content over pleasantries[1], and as such prefer to communicate in said manner.

 

[1] If you're very polite and spend 300 words on being nice without actually answering what I was wondering about, you've done nothing but waste my time. Time which I could have spent finding the answer elsewhere.

And we, in general, hate to waste time.