[SOLVED] Is This Select Statement Dangerous To My db

[HowdeeDoodee]

This statement works fine. It pulls or finds phrases in the db. The problem is that the variable coming in from the form is not processed to protect from user malicious attacks. So, is this statement dangerous to my db?

 

$query2 = "SELECT * FROM `View2_ConcordFT` WHERE `Topic` LIKE '%$_GET[seeAlso]%' OR `Subtopic` LIKE '%$_GET[seeAlso]%' AND `Source` IN ($NV,  $TR, $BT) ORDER BY `Lnum` ASC LIMIT $startrecord, $display";

 

I tried changing the Select statement above to the statement below, but the statement below pulls no records.

 

$query2 = "SELECT * FROM `View2_Concord` WHERE `Topic` LIKE '$SeeAlso%' AND `Source` IN ('NV', 'TR', 'BT')ORDER BY `Lnum` ASC LIMIT $startrecord, $display";

[trq]

So, is this statement dangerous to my db?

 

Yes, It has the potential to be. Clean the $_GET variable first then use that. eg;

 

<?php

  $SeeAlso = mysql_real_escape_string($_GET['SeeAlso']);
  $query2 = "SELECT * FROM `View2_ConcordFT` WHERE `Topic` LIKE '%$SeeAlso%' OR `Subtopic` LIKE '%$SeeAlso%' AND `Source` IN ($NV,  $TR, $BT) ORDER BY `Lnum` ASC LIMIT $startrecord, $display";

?>

 

PS: It seems you have other variables in your query also... where are they coming from?

[HowdeeDoodee]

Thank you, Thorpe for the comment. Your suggestion seems to be working fine.

 

The other variables in the Select statement come from the user form calling the php script...and now something screwy is going on. I have used the form and a similar script for some time without any issues. Now, if a variable is empty, the Select statement is still picking up values from the db like one of the $BT, $NV, or $TR variables has a value. Do you have any ideas as to why a variable that is empty is being treated by the Select statement as full?

 

Here are the variables picking up values from the form.

 

  if(!empty($BT1)){
  $BT = "'BT'";
   }
   else
   {
   $BT = "''";
   }
  if(!empty($NV1)){
  $NV = "'NV'";
   }
   else
   {
   $NV = "''";
   }
  if(!empty($TR1)){
  $TR = "'TR'";
   }
   else
   {
   $TR = "''";
   }

 

Here are query statements in an if...else construct.

$query = "SELECT * FROM `View2_ConcordFT` WHERE MATCH(`Topic`, `Subtopic`, `Theswords`) AGAINST ('$terms[0] $terms[1] $terms[2] $terms[3] $terms[4] $terms[5]' IN BOOLEAN MODE) ORDER BY `Lnum` ASC ";

} 
elseif (($boolean == TRUE) && ($split == "AND")) 
{ 

// Boolean AND query - searches for $terms[0] and $terms[1] where both terms are required, but not necessarily as a phrase. 

$query = "SELECT * FROM `View2_ConcordFT` WHERE MATCH(`Topic`, `Subtopic`, `Theswords`) AGAINST ('+$terms[0] +$terms[1] +$terms[2] +$terms[3] +$terms[4] +$terms[5]' IN BOOLEAN MODE) ORDER BY `Lnum` ASC"; 

} else { 

// non-Boolean query, searches for exact phrase entered because the user did not enter AND or OR

$query = "SELECT * FROM `View2_ConcordFT` WHERE `Topic` LIKE '%$SeeAlso%' OR `Subtopic` LIKE '%$SeeAlso%' AND `Source` IN ($NV,  $TR, $BT) ORDER BY `Lnum` ASC";
}

[btherl]

If you display your queries that your script runs, then copy and paste them into phpmyadmin (or another mysql interface), do you get the output you expect?  And are the queries what you expect them to be?

[HowdeeDoodee]

Thank you, btherl for the comment.

 

The secondary issue here is passing variable values or not passing variable values. How do I test that with phpMyAdmin?

[btherl]

I don't get what you mean by passing variable values.  You can test that variables are passed from a form into php by printing them out at the very start of your script.  You can test that they are passed into a function by printing them inside the function.  Then you can test that they are passed into your query by printing out the query and looking at it.

 

If the variables make it into the query, and you copy and paste that into phpmyadmin, but you get output you didn't expect, then you know your query is incorrect, and needs to be rewritten.