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 6 of 6
  1. #1
    New Coder
    Join Date
    Aug 2012
    Location
    France
    Posts
    57
    Thanks
    15
    Thanked 0 Times in 0 Posts

    syntax error when using POST values in query

    I am trying to build a query to check stock levels via a web page. I post the values through, print them just to check , then try to use them to build the query. The $_POST array is ok but the query fails and I get a syntax error
    Code:
    <?php
    print_r($_POST);
          // 3. perfprm database query
    	$result = mysql_query("SELECT SUM({$_POST[1]}) FROM `orders` WHERE `takenAt`= '{$_POST[0]}' and `collectDate` BETWEEN '{$_POST[2]}' AND '{$_POST[3]}' ", $connection );
    	if (!$result)
    {
    	die("sorry can't do that one: " . mysql_error());
    }
    	// 4. use returned data      
    print_r($result)
    
    ?>
    I am still rather new to this and would greatly appreciate a hint or two

    cheers

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    What's the error? And what is the value of the print_r($_POST) showing?

  • #3
    New Coder
    Join Date
    Aug 2012
    Location
    France
    Posts
    57
    Thanks
    15
    Thanked 0 Times in 0 Posts
    This are the array print and errors

    Array ( [takenAt] => Askew Road [editItem] => qty13 [date1] => 2012-11-08 [date2] => 2012-12-24 [submitEdit] => )
    sorry can't do that one: 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 ') FROM `orders` WHERE `takenAt`= '' and `collectDate` BETWEEN '' AND ''' at line 1

    thanks

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    All empty; the $_POST is indexed associatively. You need to use the names you see there as the access to the $_POST array, not the numerical offset of where it would be.
    Also, you'll want to look into SQL injection to prevent overwriting the SQL string. Binding with a prepared statement would be better, but you cannot use it in a structural element such as a property or a table name.

  • #5
    New Coder
    Join Date
    Aug 2012
    Location
    France
    Posts
    57
    Thanks
    15
    Thanked 0 Times in 0 Posts
    thanks;
    1. I understand the first bit and have substituted the actual key names. I was trying to be clever - always a mistake! I had not fully understood the difference between the two types of array. The query now runs, but I do not get the expected result. I get "Resource id #8" instead of the expected integer, (4) in this case.
    2. I am vaguely aware of SQL injection, bit do not think (??) it matters in this case as the site is private with only two users.
    3. This bit I do not understand, but will do some research to hopefully educate myself.

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    $result of a resource# is good. That means you have a resultset back; this doesn't tell you if you have any records or anything about it thourhg. You now need to fetch it, I'd suggest since you will only have one result that mysql_result be the best option
    PHP Code:
    $sum mysql_result($result0); 
    $sum will be the number you want.
    You should always put the effort into preventing sql injection regardless of the use. Using the mysql library its a simple matter of:
    1. Check if magic_quotes_gpc directive is enabled. If it is, execute stripslashes on any input data
    2. Cast the datatypes appropriate; if string (including datetime datatypes), filter through mysql_real_escape_string

    If you use prepared statements in PDO or MySQLi, then only the first step needs to occur. The statement is prepared outside of the data provided, so its not possible to inject SQL commands into data.


  •  

    Posting Permissions

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