View Full Version : PHP form field crashing query if NULL

10-13-2009, 11:00 PM
Hi, for some reason a field in a PHP form I'm using is causing my mySQL query to crash with a syntax error if the user ignores the field altogether when doing a search i.e if the field is left as null.

We need the query to work even if the field isn't filled in by the user. The field is the '_Location' field in the code below.

//include ('manager/special_offers/inc/dbconnect.php');

echo '<h2>Search for Special Offers</h2><br>';
//Include the PS_Pagination class

//Connect to mysql db
$show_form=true; //used later on

if(isset($_POST['sortform'])) {

$conn = mysql_connect('localhost', 'login', 'password');

$yoursafe_post_location = $_POST['_Location'];
$yoursafe_post_price1 = $_POST['_Price1'];
$yoursafe_post_price2 = $_POST['_Price2'];
$yoursafe_post_category = $_POST['_Category'];
$yoursafe_post_company = $_POST['_Company'];

/* check variables being set */
//echo '<p>Values:</p>';
//echo 'Location: ' . $yoursafe_post_location . '<br />';
//echo 'Price1: ' . $yoursafe_post_price1. '<br />';
//echo 'Price2: ' . $yoursafe_post_price2. '<br />';
//echo 'Category: ' . $yoursafe_post_category. '<br />';

$query = "SELECT * FROM specialofferstable WHERE ";
$and = "0";

if(trim($yoursafe_post_location)!= '') {
$and = "0";
$query .= "location LIKE '%$yoursafe_post_location%'";

if(trim($yoursafe_post_price1)!= '' && trim($yoursafe_post_price2)!= '') {
$and = "1";
if(trim($and)!= '') {$query .= " AND ";}
$query .= "price BETWEEN $yoursafe_post_price1 AND $yoursafe_post_price2";

if(trim($yoursafe_post_category)!= '') {
$and = "1";
if(trim($and)!= '') {$query .= " AND ";}
$query .= "category LIKE '$yoursafe_post_category'";

if(trim($yoursafe_post_company)!= '') {
$and = "1";
if(trim($and)!= '') {$query .= " AND ";}
$query .= "company_hotel LIKE '%$yoursafe_post_company%'";

$query .= " ORDER BY price";

The mySQL error it causes is (for example):

Could not execute query:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND price BETWEEN 0 AND 80 AND category LIKE 'hotel' AND company_hotel LIKE '%Co' at line 1

Any ideas how we can fix this?

10-13-2009, 11:11 PM
Actually ignore this, found a way just by concatenating the first two part of $query together.

10-13-2009, 11:55 PM
In a web application, you need to validate all external data when it reaches the server to insure that it contains expected values. If any particular piece of data is required, but it is empty, you need to output a message to the user telling him to provide the information. If any particular piece of data is optional, you need to set up an acceptable default value or you need to omit it from the query string. You must escape all string data (which would be enclosed in quotes in the query) that could have sql special characters entered into it that would break the syntax of the query and to help prevent sql injection. You must either validate all numeric data (which would not be enclosed in quotes in the query) to insure it is numeric or cast it as a numeric data type.