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! — alanmm
– alanmm, Commented Oct 19, 2015 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
– David, Commented Oct 19, 2015 at 15:42
Possible duplicate of When to use single quotes, double quotes, and backticks? — RiggsFolly
– RiggsFolly, Commented Oct 19, 2015 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: php.net/manual/en/pdo.prepared-statements.php. If you just want the correction to the error you are getting, see answer below. — hughjidette
– hughjidette, Commented Oct 19, 2015 at 15:57

Stavros · Accepted Answer · 2015-10-19 15:43:57Z

2

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)

answered Oct 19, 2015 at 15:43

Stavros

2643 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error when Updating SQL Table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related