Jump to content


Photo

SQL Query help


  • Please log in to reply
11 replies to this topic

#1 Andrew R

Andrew R
  • Members
  • PipPipPip
  • Advanced Member
  • 158 posts
  • LocationIreland

Posted 29 August 2006 - 09:59 AM

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?

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

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

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

$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'";


#2 ronverdonk

ronverdonk
  • Members
  • PipPipPip
  • Advanced Member
  • 277 posts
  • LocationNetherlands

Posted 29 August 2006 - 12:25 PM

I think the following covers all situations:
<?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;
?>

Ronald  8)
RTFM is an almost extinct art form, it should be subsidized.

#3 Jenk

Jenk
  • Members
  • PipPipPip
  • Advanced Member
  • 778 posts

Posted 29 August 2006 - 12:26 PM

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

#4 ronverdonk

ronverdonk
  • Members
  • PipPipPip
  • Advanced Member
  • 277 posts
  • LocationNetherlands

Posted 29 August 2006 - 12:34 PM

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)
RTFM is an almost extinct art form, it should be subsidized.

#5 Jenk

Jenk
  • Members
  • PipPipPip
  • Advanced Member
  • 778 posts

Posted 29 August 2006 - 01:04 PM

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:

<?php

$var = mysql_real_escape_string($val);

?>

No need for a '50 line class'

#6 Andrew R

Andrew R
  • Members
  • PipPipPip
  • Advanced Member
  • 158 posts
  • LocationIreland

Posted 29 August 2006 - 01:09 PM

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

#7 Orio

Orio
  • Staff Alumni
  • Advanced Member
  • 2,491 posts

Posted 29 August 2006 - 01:18 PM

Read :)
http://www.unixwiz.n...-injection.html
http://www.governmen...sicTutorial.php
And many more on google:
http://www.google.co...l 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:
<?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;
}
?>

Orio.
Think you're smarty?

(Gone until 20 to November)

#8 Jenk

Jenk
  • Members
  • PipPipPip
  • Advanced Member
  • 778 posts

Posted 29 August 2006 - 01:21 PM

Query in php:
<?php

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

?>
If the user inputs a value like:

' OR '' = '

then your query becomes:
SELECT * FROM `table` WHERE `column` = '' OR '' = ''


orio: use whitespace.. please..

#9 Orio

Orio
  • Staff Alumni
  • Advanced Member
  • 2,491 posts

Posted 29 August 2006 - 01:22 PM

Whitespace?

Orio.
Think you're smarty?

(Gone until 20 to November)

#10 Jenk

Jenk
  • Members
  • PipPipPip
  • Advanced Member
  • 778 posts

Posted 29 August 2006 - 01:23 PM

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

#11 Orio

Orio
  • Staff Alumni
  • Advanced Member
  • 2,491 posts

Posted 29 August 2006 - 01:25 PM

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

Orio.
Think you're smarty?

(Gone until 20 to November)

#12 Jenk

Jenk
  • Members
  • PipPipPip
  • Advanced Member
  • 778 posts

Posted 29 August 2006 - 01:45 PM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users