Select * where column = anything?

[wright67uk]

On my form below, I'm trying to figure a way of using my form to select, Pete, Julia or both Pete and Julia from my SQL database.What value should I put in place of ? In the below radio button values?

WCW Summary




window.onload = function() { new JsDatePick({ useMode:2, target:"inputField", dateFormat:"%d-%m-%Y" }); new JsDatePick({ useMode:2, target:"inputField2", dateFormat:"%d-%m-%Y" }); }; 






Julia

Pete

Both

From 

To 




<?php 

if(isset($_POST['submit']) && ($_POST['submit'] == "Submit")) 

{ 

echo $driver = $_POST['driver'];

echo "";

echo $date1 = $_POST['date1'];

echo "";

echo $date2 = $_POST['date2'];

echo "Date 1 - ";

echo $date1 = date("Ymd", strtotime($date1));

echo "Date 2 - ";

echo $date2 = date("Ymd", strtotime($date2));

}





$mysqli = new mysqli(connection);


if ($mysqli->connect_error) {

   die('Connect Error: ' . $mysqli->connect_error);

}


$query = "SELECT * FROM wizardlog WHERE driver='$driver' AND date between '$date1' and '$date2' "; 

$result = $mysqli->query($query);


while($row = $result->fetch_array()) 

{ $rows[] = $row; }


foreach($rows as $row) 

{ 

$id = $row['id'];

$driver = $row['driver'];

$date = date("d/m/y", strtotime($date));

$time = $row['time'];

$time = substr($time, 0, 5);

$fname = $row['fname'];

$lname = $row['lname'];

$town = $row['town'];


if ($driver === Pete) {$bgcolor = '#ff2727'; } 

else {$bgcolor = '#fffd63';}


echo '

     '.$driver." to collect ".$fname." ".$lname." from ".$town." at ".$time.'

  I 



 '; 

}

?> 

[wright67uk]

Oops that didn't post right. My form:

 

<form action="#" method="post">

<input type="radio" name="driver" value="Julia">Julia

<input type="radio" name="driver" value="Pete">Pete

<input type="radio" name="driver" value="?">Both<br>

From <input type="text" name="date1" size="12" id="inputField" />

To <input type="text" name="date2" size="12" id="inputField2" />

<input type="submit" value="Submit" name="submit"/>

</form>

[Clarding]

There's a couple of approaches you can take here, for example your BOTH value could be Pete' OR driver='Julia   Please note how the single quotes are arranged rather oddly, so that it makes sense once the sql clause is constructed.  But please please don't do that!  your code allows SQL injection and that's not something anyone should allow - too many commercial sites are prone to sql injection and we all need to make sure we never allow that to happen in our code.  

 

So, I have to say (and will probably say this over and over again in this forum!) that you should never take the submission from the form and use it directly in your SQL string.  Instead, use parameterised queries.  Using non-parameterised queries (i.e. joining together strings that use form-submitted values to construct a SQL phrase) is a big no-no for security reasons (sql injection).  Instead, you would store the values posted by your form in the SQL parameter variables, and the first two possibilities (i.e. just Julia or just Pete) will simply be WHERE `driver` = ? and then the parameter that you pass to your SQL will occupy the ? slot without having any worries about SQL injection.  (by the way, this also allows for names such as O'Connor without worrying about apostrophes that would otherwise break your code - parameterised queries really are the way to do ALL sql processing). 

 

Anyway that leads us to the third possibility, i.e. "either name".  To answer that, have your HTML value equal to "BOTH" and then in your php code say something like if ($driver === "BOTH") and construct your (parameterised) sql accordingly, i.e. with a sql statement that says WHERE Driver IN (?,?) and pass the sql statement two parameters, one for each name you are looking for. 

 

So having got this far, I then have to say the previous advice can be improved upon because it is too deeply coupled to the form, that is to say your backend code relies on the form having just two names and the "BOTH" option, so you'd then want to add refinement to your handling of the form submission so that "BOTH" is actually much more flexible, in case you later added a third or fourth name.  So instead of having "BOTH" in the value for the form field, have perhaps "Pete,Julia" as the value for the form field, and then get your php script to explode that on the comma delimeter, and you end up with a nice array of people's names.  I find myself building parameterised SQL phrases by putting as many question marks in the sql string as there are elements of the array, and passing the array of values as the sql parameters.  That way, you can have as many names as your form submits in the "Both" field, although with three or more names it'll be an "Any" field.  What you really need to aim for is to totally decouple the form and the backend php code, so that the php code can handle any number of options on the form.

 

I hope that explains things.

[Barand]

SELECT * FROM table WHERE column = anything

 

is just

 

SELECT * FROM table

[.josh]

IMO a better way to approach this is to use checkboxes instead of radio buttons (or alternatively a multi-select dropdown if you like that "look" better). This will allow you to easily add more people without having to worry about adding additional radio buttons to cover combinations of people (like "both" - what happens when you add a 3rd? you will then also have to add radio buttons for Julia and Pete, Julia and John, Pete and John, Julia and Pete and John, etc. and that will go up exponentially as you add more people!), and it will also allow you to easily make database queries.

 

Example:

 

<form action="#" method="post">
<input type="checkbox" name="driver[]" value="Julia">Julia
<input type="checkbox" name="driver[]" value="Pete">Pete
From <input type="text" name="date1" size="12" id="inputField" />
To <input type="text" name="date2" size="12" id="inputField2" />
<input type="submit" value="Submit" name="submit"/> 
</form> 

Then in your script you will receive driver as an array of values. And in your query you can use an IN operator to select where any of the values are found. Example:

 

$drivers = "'".implode("','",$_POST['driver'])."'";

$query = "SELECT * FROM wizardlog WHERE driver IN ($drivers) AND date between '$date1' and '$date2' ";

I will also note as other posters that you should sanitize the values before using them in database queries.

[Clarding]

>>"...you should sanitize the values..."

Sorry, but no, please don't rely on sanitizing values - it's not reliable to do that, everyone really ought to use parameterized queries - the sooner you get into the habit of using parameterized queries the better, then they'll become second nature to you.   Where I completely agree with Josh is that it would be better to use checkboxes, certainly.

[.josh]

>>"...you should sanitize the values..."

Sorry, but no, please don't rely on sanitizing values - it's not reliable to do that, everyone really ought to use parameterized queries - the sooner you get into the habit of using parameterized queries the better, then they'll become second nature to you.   Where I completely agree with Josh is that it would be better to use checkboxes, certainly.

Firstly, I would like to say that I only meant "sanitize your values" in the broadest of senses, as in "Don't just directly accept user input into your query." I certainly agree that using prepared statements is better than simply escaping them or whatever, but the point is that preparing your statements (among other things) effectively "sanitizes" the input. I apologize that the intention of my statement was not clear to you. I will certainly admit I could have been clearer, though to be fair, that wasn't the focus of this thread.

 

But I would also note that you should be validating for expected values regardless, which often times makes prepared statements superfluous in the security dept. For example if you expect a user to enter a number and you validate this with a regex like ~^[0-9]+$~ (or some equivalent check), and then reject the input accordingly.. well this effectively makes the security offered from the prepared statement superfluous. Of course, that's not the only thing prepared statements have to offer, so I'm not saying don't use them at all.

[wright67uk]

Thanks for the input guys, as Josh suggests, yes I really do need to get in the swing of using parameterised queries.  

I do this for inserts, but I stumble when using this method for selects.

 

 

I've had a look at the manual http://php.net/manual/en/mysqli-result.fetch-array.php ,

although i'm getting an error "Fatal error: Call to undefined method mysqli_stmt::get_result()"   

 

This is what i'm using;

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Wizard Log</title>
<link rel="stylesheet" type="text/css" media="all" href="jsDatePick_ltr.min.css" />
<script type="text/javascript" src="jsDatePick.min.1.3.js"></script>
<script type="text/javascript">
	 window.onload = function() { new JsDatePick({ useMode:2, target:"inputField", dateFormat:"%d-%m-%Y" }); new JsDatePick({ useMode:2, target:"inputField2", dateFormat:"%d-%m-%Y" }); }; 
</script>
</head>
<body>   

 <form action="#" method="post"> 
 <input type="checkbox" name="driver[]" value="Julia">Julia 
 <input type="checkbox" name="driver[]" value="Pete">Pete From 
 <input type="text" name="date1" size="12" id="inputField" /> To 
 <input type="text" name="date2" size="12" id="inputField2" /> 
 <input type="submit" value="Submit" name="submit"/> 
 </form> 

<?php 
if(isset($_POST['submit']) && ($_POST['submit'] == "Submit")) 
{ 
$date1 = $_POST['date1'];
$date2 = $_POST['date2'];
$date1 = date("Ymd", strtotime($date1));
$date2 = date("Ymd", strtotime($date2));
$drivers = "'".implode("','",$_POST['driver'])."'";
}

$mysqli = new mysqli('connection');

if($mysqli->connect_error)
{
    die("$mysqli->connect_errno: $mysqli->connect_error");
}

$query = "SELECT * FROM wizardlog WHERE driver IN (?) AND date between ? and ? ";

$stmt = $mysqli->stmt_init();
if(!$stmt->prepare($query))
{
    print "Failed to prepare statement\n";
}
else
{
			  $stmt->bind_param("sss", $drivers, $date1, $date2);
			  $stmt->execute();
    $result = $stmt->get_result();
        while ($row = $result->fetch_array(MYSQLI_ASSOC))
        {
            foreach($rows as $row) 
		{ 
		$id = $row['id'];
		$driver = $row['driver'];
		$date = date("d/m/y", strtotime($date));
		$time = $row['time'];
		$time = substr($time, 0, 5);
		$fname = $row['fname'];
		$lname = $row['lname'];
		$town = $row['town'];

if ($driver === Pete) {$bgcolor = '#ff2727'; } 
else {$bgcolor = '#fffd63';}

echo '<div>
      <div id="detail" style="background-color:'.$bgcolor.'; color:'.$color.'">'.$driver." to collect ".$fname." ".$lname." from ".$town." at ".$time.'</div>
	  <div id="info"><a href="journey_info.php?id='.$id.'"> I </a></div>
	  <div style="clear:both;"></div>
	  </div>
	  '; 
}
            
$stmt->close();
$mysqli->close();
}}

?>

</body>
</html>

Edited October 6, 2013 by wright67uk

[Ch0cu3r]

There is no method mysqli::get_result but there is a mysqli::result

[Barand]

There is no method mysqli::get_result but there is a mysqli::result

 

It's a mysqli_statement method

http://www.php.net/manual/en/mysqli-stmt.get-result.php

[wright67uk]

So which should I have used?

[Barand]

BTW, what version of PHP are you using?

 

5.3 or later is required for get_result()

[Barand]

Alternative

$stmt->execute();
$stmt->bind_result($id,$driver,$date,$time,$fname,$lname,$town); // assuming this is the order from SELECT * (which shouldn't be used)
$while ($stmt->fetch()) {
    $date = date('d/m/y', strtotime($date));
    // etc;
}

[vinny42]

 the sooner you get into the habit of using parameterized queries the better

 

If you mean "write a class that does the calls to escape_string calls for you" then yes, do it, yesterday.

If you mean "use prepared statements" then no, don't do that unless you know what you are doing.

 

Why: prepared statements are safe against injection because they parse the query before there is any data in it so the data can never be mistaken for query grammer.

Unfortunately this also means that the query planner cannot take the data into account when making the plan and so it cannot reliably choose which indexes to use

and you will regularly get queries that are very slow for no apparent reason, and there is nothing you can do about it except hope that

the database's statistics will cause the planner to reconsider soon.

 

 

this effectively makes the security offered from the prepared statement superfluous

 

Not at all, because your validation doesn't validate against SQL injection.

If for example your application expects a person's bio as a text, then you would approve

something like "I was born in 1' OR '1'='1". It's a strange text, but valid, so you let that pass, put it in the query and you're hacked.

 

Perhaps more importantly; your input validation only checks incoming data, it does not guarantee

that the data is still safe by the time it gets to the query. Safe doesn not only mean that no hackers have been at it,

it also means that the value doesn't make the query accidently destroy your data.

 

So no, always do your database related checks as near to the query as you can, and it doesn't get much closer

than a prepared statement, but a properly setup databaseclass can do the job quite well.