12 {{ upvoteCount | shortNum }}
12 {{ upvoteCount | shortNum }}

Mysql SYNTAX error - please help!

by donnan
10 replies
Hi,

I have been pulling my hair out over this syntax error. Can someone please tell me where I have it wrong.

Code:
mysql_query("UPDATE `gemps_shares` SET `given_gemps` = given_gemps + " . $gemps . ", `total_gemps` = given_gemps + link_gemps WHERE `user_id` = '$_REQUEST[user_id]' AND `user_id` = '$_REQUEST[owner_id]'") or die (mysql_error());
#error #mysql #syntax
  • Profile picture of the author phpbbxpert
    What is the error its giving?

    Try this
    PHP Code:
    mysql_query("UPDATE `gemps_shares` SET `given_gemps` = given_gemps + " . $gemps . ", `total_gemps` = given_gemps + link_gemps 
        WHERE `user_id` = '"     . $_REQUEST['user_id'] . "' 
        AND `user_id` = '"     . $_REQUEST['owner_id'] . "'") or die (mysql_error()); 
    By the way, those should all be escaped as they are strings.
    Your open to SQL Injections.
    eg. at the minimum
    PHP Code:
    mysql_query("UPDATE `gemps_shares` SET `given_gemps` = given_gemps + " . mysql_real_escape_string($gemps) . ", `total_gemps` = given_gemps + link_gemps
        WHERE `user_id` = '"     . mysql_real_escape_string($_REQUEST['user_id']) . "' AND `user_id` = '" . mysql_real_escape_string($_REQUEST['owner_id']) . "'") or die (mysql_error()); 
    {{ DiscussionBoard.errors[3526248].message }}
  • Profile picture of the author Cliff_OBA
    Why are there ' around the table and column names?

    `gemps_shares`

    could just be gemps_shares.

    If the response before mine doesn't solve your issue, try removing the extra symbols around the table and column names.

    Cliff
    {{ DiscussionBoard.errors[3528808].message }}
    • Profile picture of the author KirkMcD
      Originally Posted by IM Walkthrough View Post

      try removing the extra symbols around the table and column names.
      You don't know MySQL do you?
      Yes they aren't neccessary, but are commonly added to MySQL queries by PHPMyAdmin and other query builders.
      {{ DiscussionBoard.errors[3529525].message }}
      • Profile picture of the author Cliff_OBA
        Originally Posted by KirkMcD View Post

        You don't know MySQL do you?
        Yes they aren't neccessary, but are commonly added to MySQL queries by PHPMyAdmin and other query builders.
        Hi Kirk,

        I am not sure why you would ask if I knew MySQL based on my comment. I do not use the PHPMyAdmin query builder often... actually, I am not sure if I have ever used it. Its way too slow if you are good with SQL. If I am writing my own code, and not using some builder tool, I would only bother with quotes around tables or columns if I needed to alias them for a more complex query, which is clearly not needed here. The reason I pointed this out is that the symbols were not used consistently for every column reference, so I could not be sure who or what added them. It seemed like something to look into if it wasn't working.

        Anyways, I was just trying to provide a helpful post for the OP. And to answer your question, yes I do know MySQL.

        Cliff
        {{ DiscussionBoard.errors[3529855].message }}
        • Profile picture of the author Cash Money Hosting
          I call that sloppy / lazy coding. Just like using <?= instead of <?php echo
          Not all host support short tags btw.
          Or like using $var[test]. Throws all kinds of warnings in a dev env.

          Just my 2 cents.

          [QUOTE=IM Walkthrough]

          I do not use the PHPMyAdmin query builder often... actually, I am not sure if I have ever used it. Its way too slow if you are good with SQL. If I am writing my own code, and not using some builder tool, I would only bother with quotes around tables or columns if I needed to alias them for a more complex query, which is clearly not needed here.[ /QUOTE]
          {{ DiscussionBoard.errors[3531557].message }}
          • Profile picture of the author SteveJohnson
            Originally Posted by Cash Money Hosting View Post

            I call that sloppy / lazy coding. Just like using <?= instead of <?php echo
            Not all host support short tags btw.
            Or like using . Throws all kinds of warnings in a dev env.

            Just my 2 cents.
            You might want to watch what you call things.

            Not using backticks when constructing a MySQL query is not sloppy or lazy. Backticks are not required unless the identifier is a reserved word or contains special characters.

            Scripts like phpMyAdmin surround identifiers in backticks because it is more efficient than comparing the identifier against a reserved word table or parsing it for special characters.
            Signature

            The 2nd Amendment, 1789 - The Original Homeland Security.

            Gun control means never having to say, "I missed you."

            {{ DiscussionBoard.errors[3532006].message }}
  • Profile picture of the author phpbbxpert
    It is very common in MySQL to have ` ticks around field names and table names and single quotes around values.

    Reference to the single quotes around values: is why I suggested using mysql_real_escape_string, because using these single quotes around field values enforces that it is a string, thus allowing any input.
    {{ DiscussionBoard.errors[3529892].message }}
  • Profile picture of the author Christian Little
    Folks let's chill with the crude remarks and get back to helping the OP.

    To the OP:

    mysql_query("UPDATE `gemps_shares` SET `given_gemps` = given_gemps + " . $gemps . ", `total_gemps` = given_gemps + link_gemps WHERE `user_id` = '$_REQUEST[user_id]' AND `user_id` = '$_REQUEST[owner_id]'") or die (mysql_error());

    CHANGE TO:

    mysql_query("UPDATE `gemps_shares` SET `given_gemps` = given_gemps + " . $gemps . ", `total_gemps` = given_gemps + link_gemps WHERE `user_id` = '" . $_REQUEST['user_id'] . "' AND `user_id` = '" . $_REQUEST['owner_id'] . "'") or die (mysql_error());

    Basically what's happening is you are trying to parse the $_REQUEST variables but when you wrap them in ' it tells PHP to not parse them and use that as the literal value. So the query is literally looking for a user_id that is set to $_REQUEST[user_id] as opposed to the actual variable value.
    Signature
    cPanel Shared and Reseller Web Hosting - 30 Day Free Trial Exclusive for Warriors!
    {{ DiscussionBoard.errors[3532150].message }}
  • Profile picture of the author phpbbxpert
    @ Christian Little
    Your code is the same as mine, except now you took the escaping out.
    Whether your passing an int or not does not matter if it is inside quotes, as the quotes cause it to be parsed as a string allowing any characters to be in there. There for you are adding SQL Injection vulnerabilities.
    Just mentioning it
    {{ DiscussionBoard.errors[3532389].message }}
  • Profile picture of the author voitenkos
    Try using SUM function to add
    {{ DiscussionBoard.errors[3556166].message }}

Trending Topics