Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Sep 2009
    Posts
    165
    Thanks
    16
    Thanked 0 Times in 0 Posts

    PHP form field crashing query if NULL

    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.

    Code:
    <?php
    //include ('manager/special_offers/inc/dbconnect.php'); 
    
    echo '<h2>Search for Special Offers</h2><br>'; 
    //Include the PS_Pagination class 
    //include('manager/special_offers/inc/ps_pagination.php'); 
    
    
    //Connect to mysql db 
    $show_form=true; //used later on
    
    if(isset($_POST['sortform'])) {
    
        $conn = mysql_connect('localhost', 'login', 'password'); 
        mysql_select_db('specialoffers_db',$conn); 
        
    	
        $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):

    Code:
    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?

  • #2
    Regular Coder
    Join Date
    Sep 2009
    Posts
    165
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Actually ignore this, found a way just by concatenating the first two part of $query together.

  • #3
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,049
    Thanks
    2
    Thanked 317 Times in 309 Posts
    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.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •