Search Function with multiple variables in SQL

**cinematic_jesi** · 06-16-2009 02:23 PM

Remove those '{}' change to this

PHP Code:


$sql="SELECT * FROM $tbl_name WHERE technology = '".$_GET['technology']."' AND range = '".$_GET['range']."' AND output = '".$_GET['output']."' AND configuration='".$_GET['configuration']."'";

**cinematic_jesi** · 06-16-2009 02:27 PM

sry about messing that up, i re-edited the above post.

**cinematic_jesi** · 06-16-2009 02:37 PM

also since we went to page 2 of the thread, i wanted to make sure you saw on page 1 the sql injection code (i believe its the very last post on that page)

**themonkey40** · 06-16-2009 02:40 PM

I got the injection code. What am I supposed to put where it says "SOMEVARIABLE"?

**cinematic_jesi** · 06-16-2009 02:41 PM

that would be your $_GET variable for instance $_GET['technology'] you'll need to do it for each of them

**themonkey40** · 06-16-2009 02:41 PM

Ok I updated the code and I still get the same error code

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\Program Files\Apache Group\Apache2\htdocs\selection\product_view.php on line 24

**themonkey40** · 06-16-2009 02:42 PM

So where it says "SOMEVARIABLE", I would replace with

PHP Code:


$_GET['technology']

**cinematic_jesi** · 06-16-2009 02:43 PM

yep! $_GET['SOMEVARIABLE'] replaced to $_GET['technology'] and..

to test multiple $_GETs you can do this to the if statement

PHP Code:


if (($_GET['SOMEVARIABLE'] > '') && (!is_numeric($_GET['SOMEVARIABLE'])) || ($_GET['SOMEVARIABLE'] > '') && (!is_numeric($_GET['ANOTHERVARIABLE']))) {

The || is like an 'OR'

**themonkey40** · 06-16-2009 02:45 PM

Nice, thanks for that code. Any others I can include on my php pages to help keep them more secure?

**themonkey40** · 06-16-2009 02:46 PM

Any idea why I keep getting that error?

**cinematic_jesi** · 06-16-2009 02:54 PM

I can't seem to replicate it

.. i'm trying though

**Shorts** · 06-16-2009 03:06 PM

Change

"SELECT* FROM $tbl_name"
to
"SELECT * FROM $tbl_name"

Also, check out more about SQL Injection. All user inputted ($_POST, $_COOKIE, $_GET, etc) should be cleaned.

Personally, if the element is numeric I do intval and all else gets :

PHP Code:


$sql = "SELECT fields FROM table WHERE number=".intval($number)." AND text=".mysql_real_escape_string($text)." LIMIT 20";

Another thing, noticed that you used num_rows to get the number of rows,

try intead:

PHP Code:


$sql = "SELECT COUNT(*) FROM $tbl_name";
$sql = mysql_query($sql);
$num_rows = mysql_fetch_row($sql);
$num_rows = $num_rows[0];

Should be much faster on a larger table.

How's the other issue and can we see updated code?

**themonkey40** · 06-16-2009 03:32 PM

here is updated code without the above recommended changes (going to try, just haven't had a chance Shorts)

PHP Code:


<html>
<body>

<?php 
include "db.php";
include "error.php";

$tbl_name="product";

// Connect to server and select database.
mysql_connect($hostname, $username, $password)or die("cannot connect"); 
mysql_select_db($database)or die("cannot select DB"); 

$total_result = mysql_query( "SELECT* FROM $tbl_name" )  
    or die("SELECT Error: ".mysql_error());  

$num_rows = mysql_num_rows($total_result);  

// Retrieve data from database 
$sql="SELECT * FROM $tbl_name WHERE technology = '".$_GET['technology']."' AND range = '".$_GET['range']."' AND output = '".$_GET['output']."' AND configuration='".$_GET['configuration']."'";
$result=mysql_query($sql); 

// Start looping rows in mysql database. 
while ($rows=mysql_fetch_assoc($result)) { 
?> 

<p><?php echo $rows['product'];  ?></p>

<?php
// close while loop 
}

// close connection 
mysql_close();
?>

</body>
</html>

**themonkey40** · 06-16-2009 03:35 PM

Here is the updated code with "Shorts" recommendations.

PHP Code:


<html>
<body>

<?php 
include "db.php";
include "error.php";

$tbl_name="product";

// Connect to server and select database.
mysql_connect($hostname, $username, $password)or die("cannot connect"); 
mysql_select_db($database)or die("cannot select DB"); 

$total_result = mysql_query( "SELECT* FROM $tbl_name" )  
    or die("SELECT Error: ".mysql_error());  

// Retrieve data from database 
$sql="SELECT * FROM $tbl_name WHERE technology = '".$_GET['technology']."' AND range = '".$_GET['range']."' AND output = '".$_GET['output']."' AND configuration='".$_GET['configuration']."'";
$result=mysql_query($sql); 

$sql = "SELECT COUNT(*) FROM $tbl_name";
$sql = mysql_query($sql);
$num_rows = mysql_fetch_row($sql);
$num_rows = $num_rows[0]; 

// Start looping rows in mysql database. 
while ($rows=mysql_fetch_assoc($result)) { 
?> 

<p><?php echo $rows['product'];  ?></p>

<?php
// close while loop 
}

// close connection 
mysql_close();
?>

</body>
</html>

I still get the following error

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\Program Files\Apache Group\Apache2\htdocs\selection\product_view.php on line 27

For some reason it doesn't like "mysql_fetch_assoc():" and can't figure out why?

**Shorts** · 06-16-2009 03:50 PM

Try adding this after you set the sql like so:

PHP Code:


$sql="SELECT * FROM $tbl_name WHERE technology = '".$_GET['technology']."' AND range = '".$_GET['range']."' AND output = '".$_GET['output']."' AND configuration='".$_GET['configuration']."'";
echo "SQL: ",$sql,"<br />\n";

And see what the SQL actually looks like.

Also, rearrange the lines, the stuff I gave you, put above the $sql you already had. Right now it's being rewritten ($sql) with the code I gave and that can be causing you a new issue.

So we now have:

PHP Code:


<html>
<body>

<?php
include "db.php";
include "error.php";

$tbl_name="product";

// Connect to server and select database.
mysql_connect($hostname, $username, $password)or die("cannot connect");
mysql_select_db($database)or die("cannot select DB");

$total_result = mysql_query( "SELECT * FROM $tbl_name" )  
    or die("SELECT Error: ".mysql_error());  

// Let's get the total number of rows
$sql = "SELECT COUNT(*) FROM $tbl_name";
$sql = mysql_query($sql);
$num_rows = mysql_fetch_row($sql);
$num_rows = $num_rows[0];

// Retrieve data from database
$sql="SELECT * FROM $tbl_name WHERE technology = '".$_GET['technology']."' AND range = '".$_GET['range']."' AND output = '".$_GET['output']."' AND configuration='".$_GET['configuration']."'";
// Let's now print the sql to screen to see what we have :D
echo "SQL: ",$sql,"<br />\n";
$result=mysql_query($sql);

// Start looping rows in mysql database.
while ($rows=mysql_fetch_assoc($result)) {
?>

<p><?php echo $rows['product'];  ?></p>

<?php
// close while loop
}

// close connection
mysql_close();
?>

</body>
</html>

Thread: Search Function with multiple variables in SQL

Thread Tools

Display

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions