Why am I receiving the error "Unknown column 'deposit' in 'field list'"

Question

I have created a simple HTML form for submitting an amount of money that should be deposited into an account. The form consists of a number input and a submit button.

When I press the submit button after inputting 10 in the input box I get this following error message:

Error updating record: Unknown column 'deposit' in 'field list'

I don't have such a column in my table as can be seen on the following image:

Welcome to the Bank! <br><br>
<table bgcolor="orange" border="1" cellspacing="0" cellpadding="50">

<tr><td>
<form action="" method='post'><b>Amount of money you want to deposit:</b><br><br>
<input type='number'><input name ='deposit' type='submit' value='deposit'></td></form>

<?php include 'db.php';
if (isset($_POST['deposit']))
{
    $deposit = $_POST['deposit'];
    
   $sql = "UPDATE bank SET bank = bank + $deposit, cash_on_hand = cash_on_hand - $deposit WHERE id=1";
   
   if ($conn->query($sql) === TRUE)
   {
     echo "Record updated successfully"; /*Money Deposited successfully!*/
   }
   else
   {
     echo "Error updating record: " . $conn->error;
   }
   $conn-> close();
}
?>

Please update your question with the correct code snippet. (Btw, it seems to be that your database table you want to insert data in doesn't have the needed `deposit` column.) — Zoli Szabó, Feb 02 '21 at 20:04
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Feb 02 '21 at 20:05
I have no idea what you want to ask. You send a value `deposit` which is injected into SQL and treated as a column name. Use prepared statements to avoid silly mistakes like this. — Dharman, Feb 02 '21 at 20:07
im trying to update it but i cant get past this- Please add some context to explain the code sections (or check that you have not incorrectly formatted all of your question as code). ive added ``` before and after the code but its not having it! — Phil Gibson, Feb 02 '21 at 20:07
If you are only starting to learn PHP then you should learn PDO instead of mysqli. PDO is much easier and more suitable for beginners. Start here https://phpdelusions.net/pdo & https://websitebeaver.com/php-pdo-prepared-statements-to-prevent-sql-injection — Dharman, Feb 02 '21 at 20:08
heres my code: https://imgur.com/7ShSwvV also im not bothering with security atm. just playing around. ive got the db info to display in browser, now im trying to deposit a value i enter into the deposit box but i cant get past that error — Phil Gibson, Feb 02 '21 at 20:10
`not bothering with security` ...irrelevant, because parameterisation solves more than just security issues. And anyway if you're just playing around, that's the time to learn how to use them so that when you come to write a real application, you already know what to do. — ADyson, Feb 02 '21 at 20:16
I see now. Even when you fix SQL injection, your main problem remains. You have no idea how HTML forms work. This `` doesn't do what you think it does. Likely, it should be `` — Dharman, Feb 02 '21 at 20:18
i am learning. im going through the w3schools tutorials & i spent 1hr getting the database info to display on browser But atm ive spent 6hrs trying to move x amount from cash_in_hand to bank. hence why ive come here. — Phil Gibson, Feb 02 '21 at 20:19
Don't use w3schools for learning PHP. They have terrible tutorials. If you want to learn database interactions then use the links I gave you. For HTML form you can use MDN https://developer.mozilla.org/en-US/docs/Learn/Forms — Dharman, Feb 02 '21 at 20:21
I second that, w3schools is usually either obsolete or riddled with errors / bad practice. — ADyson, Feb 02 '21 at 20:25
@Dharman your change of form input has worked for when i deposited 10. but when i click on the deposit button and the input box is empty. i receive this error: Error updating record: 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 ', cash_on_hand = cash_on_hand - WHERE id=1' at line 1 do you know why? id like to add something so when i click deposit and the input box is either 0 or empty then it displays a msg saying something like "amount needs to be 1 or greater". ty — Phil Gibson, Feb 02 '21 at 20:36
@PhilGibson Once again. This is called SQL injection. You must fix it immediately. Please see my answer — Dharman, Feb 02 '21 at 20:38

Dharman · Accepted Answer · 2021-02-02T20:41:51.247

You have two problems with this code. The biggest one is that you have SQL injection. This is the reason why you get that error message.

You should never ever inject variables directly into SQL. This will break your SQL and cause errors which can even be abused by attackers. When you submit the form by pressing on the button the value of the button is sent to PHP. This value is then injected into SQL.

// The value comes from <input name ='deposit' type='submit' value='deposit'>
$deposit = $_POST['deposit'];
$sql = "UPDATE bank SET bank = bank + $deposit, cash_on_hand = cash_on_hand - $deposit WHERE id=1";
// this becomes an invalid query
$sql = "UPDATE bank SET bank = bank + deposit, cash_on_hand = cash_on_hand - deposit WHERE id=1";

Always use prepared statements with parameter binding.

include 'db.php';
if (isset($_POST['deposit'])) {
    $deposit = $_POST['deposit'];

    $stmt = $conn->prepare('UPDATE bank SET bank = bank + ?, cash_on_hand = cash_on_hand - ? WHERE id=1');
    $stmt->bind_param('ss', $deposit, $deposit); //bind the same variable twice for the two placeholders
    $stmt->execute();
}

Your second problem is the broken HTML form. Only <input>s with name are submitted to the server. The submit button doesn't need a name, but the number input field does. When fixed it should look something like this:

<form action="" method='post'>
    <b>Amount of money you want to deposit:</b>
    <br><br>
    <input type='number' name='deposit'>
    <input type='submit'>
</form>

If you want to learn more about HTML forms there is a guide on MDN.

score 0 · Answer 2 · answered Feb 02 '21 at 20:32

Change this:

<input type='number'><input name ='deposit' type='submit' value='deposit'>

to this:

<input type='number' name="deposit"/><input name='submit' type='submit' value='deposit'/>

The way you've got it now, it sends the word "deposit" to your server, because that's the value of the button (which is the field which has the name="deposit". Meanwhile, your number field doesn't have a name at all, and therefore doesn't get submitted with the form.

That's the root cause of your error because your SQL (due to the lack of proper parameterised queries) ends up as

UPDATE bank SET bank = bank + deposit, cash_on_hand = cash_on_hand - deposit WHERE id=1

And of course deposit, as you rightly say, isn't a column in your table.

Why am I receiving the error "Unknown column 'deposit' in 'field list'"

2 Answers2