BeginnerLesson 9 of 306 min read

Changing Data Safely: INSERT, UPDATE, DELETE and Parameters

Add, change and remove rows on a practice copy, undo mistakes with transactions and keep typed text out of your SQL with placeholders.

In this lesson you will

  • Use INSERT, UPDATE and DELETE
  • Undo changes with a transaction
  • Preview a change with a SELECT
  • Use placeholders instead of joining text

About 28 minutes

The day Maya changed every price

So far you have only read data. Real databases also change. Maya adds new customers, corrects prices and removes cancelled test orders. Each change is one of three statements: INSERT adds rows, UPDATE changes existing rows and DELETE removes rows. Reading data is safe, because you can always run the query again. Changing data is different, and one forgotten condition can alter every row. This lesson shows the three statements, the safety net called a transaction and a habit that protects your queries against bad input.

The three statements

  • INSERT INTO ... VALUES: adds one row. List the values in the order of the columns, or name the columns first.
  • UPDATE ... SET ... WHERE: changes the named columns in every row the WHERE keeps. With no WHERE, it changes every row.
  • DELETE FROM ... WHERE: removes every row the WHERE keeps. With no WHERE, it empties the table.

Transactions: a way to undo

A transaction groups changes so they succeed or fail together. You make the changes, then either commit them to make them permanent, or roll back to undo everything since the transaction began. In the Python library used below, a transaction begins automatically when a data-changing statement runs, in its default mode, and it ends at a commit or rollback. Other tools differ in how a transaction starts and whether changes commit automatically, so check yours before relying on a rollback. The same idea is what lets a bank move money out of one account and into another without losing it half way.

Change a copy, step by step

The program below builds a fresh copy of the Fernleaf database from the script of lesson 1, saved as a file called shop.sql. It then makes three experiments inside transactions, rolling each back, and finally commits a deliberate insert. It also compares two ways of putting a typed value into a query, which we will come to in a moment.

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript(open("shop.sql").read())      # the script from lesson 1

def count(sql, args=()):
    return db.execute(sql, args).fetchone()[0]

# 1) change data inside a transaction, then undo it
over = "SELECT COUNT(*) FROM products WHERE category = 'plant' AND price > 6.5"
print("plants over 6.5 before:", count(over))
cur = db.execute("UPDATE products SET price = price + 1 WHERE category = 'plant'")
print("rows changed:", cur.rowcount, "| plants over 6.5 now:", count(over))
db.rollback()
print("after rollback:", count(over))
cur = db.execute("DELETE FROM order_items WHERE order_id = 4")
print("rows deleted:", cur.rowcount, "| order_items left:", count("SELECT COUNT(*) FROM order_items"))
db.rollback()
print("after rollback:", count("SELECT COUNT(*) FROM order_items"))

# 2) an UPDATE without WHERE changes every row
before = count("SELECT COUNT(*) FROM products WHERE stock = 0")
db.execute("UPDATE products SET stock = 0")
print("rows with stock 0, before and after:", before, count("SELECT COUNT(*) FROM products WHERE stock = 0"))
db.rollback()

# 3) building SQL from text versus using placeholders
typed = "' OR '1'='1"
unsafe = f"SELECT COUNT(*) FROM customers WHERE name = '{typed}'"
print("joined into the query:", count(unsafe), "rows match")
print("with a placeholder:", count("SELECT COUNT(*) FROM customers WHERE name = ?", (typed,)), "rows match")
print("a real name, with a placeholder:", count("SELECT COUNT(*) FROM customers WHERE name = ?", ("Asha",)), "row matches")

# 4) saving a change on purpose
db.execute("INSERT INTO customers VALUES (7, 'Gus', 'Hull', '2026-10-25')")
db.commit()
print("customers after the insert:", count("SELECT COUNT(*) FROM customers"))
plants over 6.5 before: 3
rows changed: 4 | plants over 6.5 now: 4
after rollback: 3
rows deleted: 1 | order_items left: 18
after rollback: 19
rows with stock 0, before and after: 1 8
joined into the query: 6 rows match
with a placeholder: 0 rows match
a real name, with a placeholder: 1 row matches
customers after the insert: 7

The first experiment adds 1 to every plant’s price. Before the update, 3 plants cost more than 6.5. The update reported 4 rows changed, and afterwards 4 plants cost more than 6.5, because the cactus crossed the line. After the rollback the count returned to 3, as if nothing had happened. The delete removed 1 row, leaving 18 order items, and rollback restored all 19. The next experiment is the dangerous one. An UPDATE with no WHERE set the stock of every product to 0. The number of products with stock 0 went from 1 to 8, including the mist spray, whose stock had been unknown. Rolling back saved the day. On a real database without that safety net, the only cure would be a backup.

Putting typed text into a query

The last block of the program shows the most important habit in this lesson. Suppose a search box passes the typed text into a query. The program tries the text ‘ OR ‘1’=’1, which looks like a strange name but is also valid SQL. When the text is joined into the query string, the database reads it as part of the SQL, the condition becomes always true, and the query matches 6 rows, every customer. This is called SQL injection, and on a real site it can expose or alter data. When the same text is passed as a parameter, written as a question mark placeholder with the value supplied separately, the database treats it only as a value. It matches 0 rows, because nobody has that odd name. A real name, passed the same way, matched 1 row. Security guidance is consistent that parameterised queries, also called prepared statements, are the first defence, because they keep code and data apart.

The final line shows a deliberate commit: after inserting Gus and committing, the table holds 7 customers. The change would survive a restart because it was committed.

Safe habits for changing data

  1. Write the WHERE first. Run it as a SELECT and count the rows before you turn it into UPDATE or DELETE.
  2. Work inside a transaction where your tool allows it, and check the row count it reports.
  3. Practise on a copy, and keep backups of anything you cannot recreate.
  4. Never build SQL by joining typed text into a string. Use placeholders.
  5. Give programs the least access they need, such as a read-only account for reports.
Common mistake: changing data without checking the rows

Running UPDATE or DELETE without a WHERE, or with a WHERE you have not tested. Always preview with a SELECT first.

A practice copy only

The database is a made-up copy and every change here was made in memory. How transactions start and commit, and how placeholders are written, differ between databases and libraries, so check your documentation. This lesson is an outline of good habits, not a complete security guide.

On your own copy, write a SELECT for the rows you would delete from order_items for one cancelled order. Count them, then turn it into a DELETE inside a transaction and roll it back.

Practice for this lesson

Check what you learned

Five quick questions. Pick an answer and you will see straight away whether it is right and why.

What does an UPDATE without WHERE do?

  1. Changes nothing
  2. Changes the first row
  3. Deletes the table
  4. Changes every row in the table

The stock of all eight products became 0.

What does a rollback do?

  1. Saves the changes
  2. Undoes the changes since the transaction began
  3. Deletes the database
  4. Sorts the table

After the rollback the counts returned to their old values.

What is the safest first step before a DELETE?

  1. Run DELETE and see
  2. Remove the WHERE
  3. Run the same WHERE as a SELECT and count the rows
  4. Delete twice

A preview shows which rows would go.

Why did the joined-in text match every customer?

  1. The database read the typed text as part of the SQL
  2. There was a customer with that name
  3. The table was empty
  4. The placeholder was missing

Joining text into the query lets it change the query.

Why use placeholders for typed values?

  1. They make queries shorter
  2. They sort results
  3. They keep code and data apart, so typed text stays a value
  4. They avoid COMMIT

Parameterised queries are the first defence against injection.

Summary

  • INSERT adds, UPDATE changes and DELETE removes rows.
  • A missing WHERE changes every row.
  • Transactions let you commit or roll back.
  • Use parameters, never join typed text into SQL.

Next lesson

Lesson 10 is the beginner project: answering real business questions from the Fernleaf database.

Questions

Can I undo a change after committing it?

Not with a rollback. After a commit, only a backup or a deliberate reverse change can restore the data.

Do all databases use the same placeholder?

No. A question mark is common, but some systems use names or numbers. Check your library and database documentation.

Is SQL injection only a web problem?

It can happen anywhere a program builds SQL from outside text. Placeholders protect every kind of program.

Progress is saved in this browser.