Help with variable interpolation in WHER clause

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Jerry
    #1

    Help with variable interpolation in WHER clause

    I am a MySQL and PHP newbie. I am having trouble getting the $w variable in
    my code below passed to mysql. When I use the value of $w directly in the
    Where clause, the correct rows are returned. However, when I try to use the
    variable in the Where clause, either an error occurs or no rows are
    returned. Any thoughts greatly appreciated!

    I am using php-4.4.2 and MySQL-4.1.18-0 on Suse 9.1

    Jerry

    <html>
    <body>
    <?php
    $link = mysql_connect(' localhost:3306' , 'mysql');
    mysql_select_db ('test_ermd') or die('Could not select database');
    $w = 'exec';
    // I have tried all the variations below to get the value of $w to process
    in the WHERE clause
    //------------------------------------------------------------------------------------------
    //$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
    FROM officers WHERE GRP = "exec"';
    //--This works: all rows with 'exec' in GRP column are selected

    //$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
    FROM officers WHERE GRP = $w';
    //--Invalid query: Unknown column '$w' in 'where clause'
    //--Whole query: SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX,
    EMAIL FROM officers WHERE GRP = $w

    //$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
    FROM officers WHERE GRP = \"$w\"';
    //--Invalid 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 '\"$w\"' at line 1
    //--Whole query: SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX,
    EMAIL FROM officers WHERE GRP = \"$w\"

    //$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
    FROM officers WHERE GRP = \$w';
    //--Parse error: syntax error, unexpected T_STRING
    in /usr/local/bin/apache2/htdocs/test_ermd.php on line 34

    //$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
    FROM officers WHERE GRP = "\$w"';
    //--No rows are selected

    //$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL
    FROM officers WHERE GRP = \'$w\'';
    //--No rows are selected
    //------------------------------------------------------------------------------------------

    echo 'Raw query: ' . $query;
    $result = mysql_query($qu ery);
    if (!$result) {
    $message = 'Invalid query: ' . mysql_error() . "<br>";
    $message .= 'Whole query: ' . $query;
    die($message);
    }
    while ($row = mysql_fetch_ass oc($result)) {
    foreach ($row as $key => $value) {
    if ($value) {
    echo "$value";
    echo "<br>";
    }
    }
    echo "<p>";
    }
    mysql_free_resu lt($result);
    mysql_close($li nk);
    ?>
    </body>
    </html>
    //=============== =============== =============== =============== ========
    Tags: None
    • Sjoerd
      #2
      Re: Help with variable interpolation in WHER clause

      > //$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX, EMAIL[color=blue]
      > FROM officers WHERE GRP = $w';[/color]

      When a string is between 'single quotes', variables are not parsed.
      This means that after
      $query = 'SELECT $w'
      The variable query literaly contains SELECT $w. To use the value of the
      variable w instead, use double quotes:
      $query = "SELECT $w"
      This becomes SELECT and then the value of variable w.

      Another option is to use the concatination operator, which is a dot.
      $query = 'SELECT '.$w
      This way, the string 'SELECT ' and the variable w are concatenated
      together.

        Comment

        • Alan Little
          #3
          Re: Help with variable interpolation in WHER clause

          Carved in mystic runes upon the very living rock, the last words of
          Sjoerd of comp.lang.php make plain:
          [color=blue][color=green]
          >> //$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX,
          >> EMAIL FROM officers WHERE GRP = $w';[/color]
          >
          > When a string is between 'single quotes', variables are not parsed.
          > This means that after
          > $query = 'SELECT $w'
          > The variable query literaly contains SELECT $w. To use the value of
          > the variable w instead, use double quotes:
          > $query = "SELECT $w"
          > This becomes SELECT and then the value of variable w.
          >
          > Another option is to use the concatination operator, which is a dot.
          > $query = 'SELECT '.$w
          > This way, the string 'SELECT ' and the variable w are concatenated
          > together.[/color]

          And don't forget to enclose the value in single quotes within the query:

          "...WHERE GRP = '$w'";

          --
          Alan Little
          Phorm PHP Form Processor
          http://www.phorm.com/

            Comment

            • Jerry Stuckle
              #4
              Re: Help with variable interpolation in WHER clause

              Alan Little wrote:[color=blue]
              > Carved in mystic runes upon the very living rock, the last words of
              > Sjoerd of comp.lang.php make plain:
              >
              >[color=green][color=darkred]
              >>>//$query = 'SELECT TITLE, NAME, INST, ADD1, ADDR2, ADDR3, PH, FAX,
              >>>EMAIL FROM officers WHERE GRP = $w';[/color]
              >>
              >>When a string is between 'single quotes', variables are not parsed.
              >>This means that after
              >>$query = 'SELECT $w'
              >>The variable query literaly contains SELECT $w. To use the value of
              >>the variable w instead, use double quotes:
              >>$query = "SELECT $w"
              >>This becomes SELECT and then the value of variable w.
              >>
              >>Another option is to use the concatination operator, which is a dot.
              >>$query = 'SELECT '.$w
              >>This way, the string 'SELECT ' and the variable w are concatenated
              >>together.[/color]
              >
              >
              > And don't forget to enclose the value in single quotes within the query:
              >
              > "...WHERE GRP = '$w'";
              >[/color]

              Only if it's non-numeric.

              --
              =============== ===
              Remove the "x" from my email address
              Jerry Stuckle
              JDS Computer Training Corp.
              jstucklex@attgl obal.net
              =============== ===

                Comment

                • TristaSD
                  #5
                  Re: Help with variable interpolation in WHER clause

                  WHERE GRP = '$w' . Had the same problem, but tinkered it into
                  submission =)

                    Comment

                    • Jerry
                      #6
                      Re: Help with variable interpolation in WHER clause

                      TristaSD wrote:
                      [color=blue]
                      > WHERE GRP = '$w' . Had the same problem, but tinkered it into
                      > submission =)[/color]

                      Thanks to all! The single-quoted variable (textual value) within a
                      double-quoted SQL statement returns all the desired rows.

                      Jerry

                        Comment

                        • Andy Jeffries
                          #7
                          Re: Help with variable interpolation in WHER clause

                          On Thu, 18 May 2006 15:01:31 -0400, Jerry Stuckle wrote:[color=blue][color=green]
                          >> And don't forget to enclose the value in single quotes within the query:
                          >>
                          >> "...WHERE GRP = '$w'";
                          >>[/color]
                          > Only if it's non-numeric.[/color]

                          It's actually a good idea to do it even if the field is non-numeric. Say
                          you have a form that does:

                          DELETE FROM PrivateMessages
                          WHERE ID=$id AND OwnedByID=$_SES SION["memberid"];

                          You think you're being safe as you're ensuring a member can only delete
                          PrivateMessages that are owned by themselves.

                          However, what if a malicious user changed the value of $id from "2" to
                          "ID OR 1=1". Easy enough to do by saving the page, editing the field
                          value and hitting submit. You then get the SQL statement:

                          DELETE FROM PrivateMessages
                          WHERE ID=ID OR 1=1 AND OwnedByID=$_SES SION["memberid"];

                          This would delete all PrivateMessages (as the value of the ID column
                          always equals itself and the additional OR 1=1 gives a positive left hand
                          side to the following AND).

                          The next question could easily be "but how would the attacker know the
                          name of the field, I don't use ID as my field name". Easy, a)it could
                          have been revealed in an error message if they're displayed on the site
                          and b)this error message may have been fixed, but previously cached by
                          Google.

                          Even if the attacker doesn't know the field name, they could set the
                          variable contents to be something like "0 OR 1=1 OR 1=1" which then gives
                          the SQL statement:

                          DELETE FROM PrivateMessages
                          WHERE ID=0 OR 1=1 OR 1=1 AND OwnedByID=$_SES SION["memberid"];

                          Which then always works.

                          It's always a good idea to wrap field values in '', even if they're
                          numeric as it stops this kind of attack. If you did so, the SQL statement
                          would be:

                          DELETE FROM PrivateMessages
                          WHERE ID='ID OR 1=1' AND OwnedByID=$_SES SION["memberid"];

                          And the numeric ID column will never equal that string, no records deleted.

                          Of course, you also need to ensure the field is safe using
                          mysql_real_esca pe_string, but that's a separate conversation to this one
                          about wrapping numeric field values in ''.

                          Cheers,


                          Andy



                          --
                          Andy Jeffries MBCS CITP ZCE | gPHPEdit Lead Developer
                          http://www.gphpedit.org | PHP editor for Gnome 2
                          http://www.andyjeffries.co.uk | Personal site and photos

                            Comment

                            • Andy Jeffries
                              #8
                              Re: Help with variable interpolation in WHER clause

                              On Thu, 18 May 2006 20:20:14 +0000, Andy Jeffries wrote:[color=blue][color=green][color=darkred]
                              >>> And don't forget to enclose the value in single quotes within the
                              >>> query:
                              >>>
                              >>> "...WHERE GRP = '$w'";
                              >>>[/color]
                              >> Only if it's non-numeric.[/color]
                              >
                              > It's actually a good idea to do it even if the field is non-numeric.[/color]

                              I mean "is numeric" there, I guess it's obvious from the rest of the post,
                              but hey I'm a programmer I have to try and correct my mistakes... :-)

                              Cheers,


                              Andy


                              --
                              Andy Jeffries MBCS CITP ZCE | gPHPEdit Lead Developer
                              http://www.gphpedit.org | PHP editor for Gnome 2
                              http://www.andyjeffries.co.uk | Personal site and photos

                                Comment

                                • Alan Little
                                  #9
                                  Re: Help with variable interpolation in WHER clause

                                  Carved in mystic runes upon the very living rock, the last words of
                                  Jerry Stuckle of comp.lang.php make plain:
                                  [color=blue]
                                  > Alan Little wrote:[color=green]
                                  >>
                                  >> And don't forget to enclose the value in single quotes within the
                                  >> query:
                                  >>
                                  >> "...WHERE GRP = '$w'";[/color]
                                  >
                                  > Only if it's non-numeric.[/color]

                                  In his example it was "exec".

                                  Plus what Andy said.

                                  --
                                  Alan Little
                                  Phorm PHP Form Processor
                                  http://www.phorm.com/

                                    Comment

                                    Previous template Next
                                    Working...