Error when Updating SQL Table

Question

I'm trying to have a form to update a table in my SQL database but I'm getting this error

If my Client ID field has "7020" as the value and the Proof field as "test" I get this error: Could not update data: Unknown column 'test' in 'field list'

</head>

   <body>
      <?php
         if(isset($_POST['update']))
         {
            $dbhost = 'xxxxxxxx';
            $dbuser = 'xxxxx';
            $dbpass = 'xxxxxxxxxxxxxxxxxxxxxxxx';

            $conn = mysql_connect($dbhost, $dbuser, $dbpass);

            if(! $conn )
            {
               die('Could not connect: ' . mysql_error());
            }

            $clientid = $_POST['clientid'];
            $proof = $_POST['proof'];

            $sql = "UPDATE penalties ". "SET Proof = $proof " ."WHERE client_id = $clientid AND type='ban'";
            mysql_select_db('b3bot');
            $retval = mysql_query( $sql, $conn );

            if(! $retval )
            {
               die('Could not update data: ' . mysql_error());
            }
            echo "Updated data successfully\n";

            mysql_close($conn);
         }
         else
         {
            ?>
               <form method="post" action="<?php $_PHP_SELF ?>">
                  <table width="400" border="0" cellspacing="1" cellpadding="2">

                     <tr>
                        <td width="100">Client ID</td>
                        <td><input name="clientid" type="text" id="clientid"></td>
                     </tr>

                     <tr>
                        <td width="100">Proof</td>
                        <td><input name="proof" type="text" id="proof"></td>
                     </tr>

                     <tr>
                        <td width="100"> </td>
                        <td> </td>
                     </tr>

                     <tr>
                        <td width="100"> </td>
                        <td>
                           <input name="update" type="submit" id="update" value="Update">
                        </td>
                     </tr>

                  </table>
               </form>
            <?php
         }
      ?>
   </body>
</html>

Abandon `mysql_` functions, for they art deprecated! Thou shall use `mysqli_` ones, or `PDO` objects at the most! — al'ein, Oct 19 '15 at 15:41
It sounds like you're just not quoting string values in your query. Though since this code is wide open to SQL injection, technically you could be trying to execute any arbitrary SQL code that the user is sending you. — David, Oct 19 '15 at 15:42
Possible duplicate of [When to use single quotes, double quotes, and backticks?](http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks) — RiggsFolly, Oct 19 '15 at 15:55
I agree with @David, please sanitize your inputs, at least use prepared statements to help make the user input less dangerous. See here: http://php.net/manual/en/pdo.prepared-statements.php. If you just want the correction to the error you are getting, see answer below. — hughjidette, Oct 19 '15 at 15:57

score 2 · Accepted Answer · answered Oct 19 '15 at 15:43

The sql query you are trying to execute should be wrong. As I see, the query right now looks like:

UPDATE penalties SET Proof = sth WHERE client_id = test AND type='ban'

should be like:

UPDATE penalties SET Proof = 'sth' WHERE client_id = 'test' AND type='ban'

(note the quotes)

Error when Updating SQL Table

1 Answers1