SQL Query help

[Andrew R]

How would I set my forms and the SQL query (below) so if I didn't want to view by a specific option (aircraft, Departing_City and Arriving_City) the users could click Any from the drop down box and view all the results from the database?

[code]<select name="search" id="search">
  <option value="">Any</option>
          <?php print $select_options ; ?> </select>[/code]

[code]<select name="search2" id="search2">
  <option value="">Any</option>
          <?php print $select_options2 ; ?> </select>[/code]

[code]<select name="search3" id="search3">
          <option value="">Any</option>
          <?php print $select_options3 ; ?> </select>[/code]

[code]
$search=$_POST["search"];
$search2=$_POST["search2"];
$search3=$_POST["search3"];

$query_flights = "SELECT * FROM Schedules WHERE Aircraft = '$search' AND Departing_City = '$search2' AND Arriving_City = '$search3'";[/code]

[ronverdonk]

I think the following covers all situations:
[code]<?php
$search =$_POST["search"];
$search2=$_POST["search2"];
$search3=$_POST["search3"];

$dep = " Departing_City = '$search2' ";
$arr = " Arriving_City = '$search3' ";
$sql = "SELECT * FROM Schedules ";
$sub = "";

if ($search2 && !$search3)
  $sub .= "WHERE $dep";
if ($search2 && $search3)
  $sub .= "WHERE $dep AND $arr";
if ($search3 && !$search2)
  $sub .= "WHERE $arr";
if ($search)
  $sub  = "";

$query_flights = $sql.$sub;
echo $query_flights;
?>[/code]

Ronald  8)

[Jenk]

Sanitise your user input. An attacker could ruin your day with the above snippet.

[ronverdonk]

I don't  say that you are wrong, but you can't fuss about samples having no sanitizing input.

People show parts of their code because they have a problem. Since it many times involves very much code, they only show snippets. I myself use a 50 lines class to sanitize input, but I would'nt show that in every question I pose to the forum.

Ronald  8)

[Jenk]

Yes, it very much matters when posting snippets. 9/10 the other poster will just c+p what you post, thus their application will now be wide open to problems.

all it takes is this:

[code]<?php

$var = mysql_real_escape_string($val);

?>[/code]

No need for a '50 line class'

[Andrew R]

Cheers, though I can't see how that script would be open to attackers?  I'm getting worried now  :o

[Orio]

Read :)
http://www.unixwiz.net/techtips/sql-injection.html
http://www.governmentsecurity.org/articles/SQLinjectionBasicTutorial.php
And many more on google:
http://www.google.com/search?hl=en&lr=&q=sql+injection+php


For escaping, I suggest you to use the function I added below rather than mysql_real_escape_string(). Because on some servers magic quotes are on and on some it's off.
Here:
[code]<?php
function sql_quote($value)
{
    if(get_magic_quotes_gpc())
    {
        $value = stripslashes($value);
    }
   
    if(function_exists("mysql_real_escape_string"))
    {
        $value = mysql_real_escape_string($value);
    }
    else
    {
        $value = addslashes($value);
    }
    return $value;
}
?>[/code]

Orio.

[Jenk]

Query in php:
[code]<?php

$sql = "SELECT * FROM `table` WHERE `column` = '{$_POST['value']}'";

?>[/code]
If the user inputs a value like:

[quote]' OR '' = '[/quote] then your query becomes:
[code]SELECT * FROM `table` WHERE `column` = '' OR '' = ''[/code]


orio: use whitespace.. please..

[Orio]

Whitespace?

Orio.

[Jenk]

Yes, your function is about as readable as a hebrew book to an Englishman..

[Orio]

lol what does it matter how it looks like? It's important it works :)
And I have no problem reading hebrew books either... ???

Orio.

[Jenk]

Either way, thanks for changing it. I could read it, but it's not easy on the eyes and to a complete beginner to PHP it might not be pleasant to decipher.