Lesson 3 - MySQL step by step: Inserting And Deleting Table Data
In the previous lesson, MySQL step by step: Creating a Database And a Table, we created the database and the user table in it. In today's MySQL tutorial, we're going to insert and delete the entries, which means our users.
Inserting an entry into the table
We're going to use phpMyAdmin first to insert a new user. Open the
user table. In the top bar, select Insert. We'll
only fill in the fields in the Value column and leave the
field blank. This one is going to be filled automatically thanks to the
AUTO_INCREMENT property. Fill the field contents in as you need and
confirm with the Go button:
Now open the table and see that John Smith is really stored in the database.
phpMyAdmin displayed another SQL query, which is
insert John Smith, we could also execute this query:
INSERT INTO `user` ( `first_name`, `last_name`, `birth_date`, `articles_count` ) VALUES ( 'John', 'Smith', '1984-11-03', 17 );
The first line is clear again, we simply say, "Insert into user", the next lines specify the columns in which the new item will have some values. We don't mention the id column here. Then the word values follows along with a list of elements in brackets. This time, these are the actual values. They go in the order of the column names. The text values are always in quotes or apostrophes, all the values are separated by commas.
WARNING! When we enter text into the SQL query (e.g., the name of the user here), it cannot contain quotes, apostrophes, and few other characters. Of course, these characters can appear in the text, we just have to sanitize them so the database don't think they're part of the query. We'll get to it later. Insert several users using SQL queries, if you don't have fantasy, just paste those from the table from the introductory lesson:
Let's try to delete some user. You would probably figure out that it's done by the red Delete button. Try it.
In the SQL, we delete entries using the
DELETE FROM `user` WHERE `user_id` = 2;
Try it, phpMyAdmin is careful about
DELETE and it'll ask you
whether you're sure.
The command is simple, we say "delete from
user where the value
user_id column equals
2". Let's focus on the
WHERE clause which defines the condition. We're going to encounter
it in other queries as well. Since we delete here by the primary key, we can be
sure that we always delete one user only. Of course, we can use a more complex
condition, use parentheses and the
DELETE FROM `user` WHERE (`first_name` = 'John' AND `birth_date` >= '1980-1-1') OR (`articles_count` < 3);
The command above deletes all Johns who were born after 1980 or all the users who wrote less than 3 articles.
WARNING, never forget the WHERE clause.
If you write just:
DELETE FROM `user`;
All the users in the table will be deleted.
SQL injection is a term referring to a violation of the database by malicious code from the user.
I've decided to insert this chapter right at the beginning of the course. If you get confused, don't worry about it, it's just so you knew about the risk. We'll always show how to communicate with the database safely in courses for the given programming language.
What's an SQL injection
Imagine that our
user table is part of some application's
database. And also that we allow the users (of our application) to delete users
by their last name. Therefore, we'll put a variable carrying a value from the
user into the query:
DELETE FROM `user` WHERE `last_name` = '$last_name';
$last_name is a variable that may contain the following
The final query will look like this:
DELETE FROM `user` WHERE `last_name` = 'Smith';
The query is executed and all the Smiths are deleted. That sounds like what we wanted. Now imagine what happens when someone enters this to the variable:
' OR 1 --
The resulting query would look like this:
DELETE FROM `user` WHERE `last_name` = '' OR 1 --';
1 is always true from the logical point of view, the
condition results in that either the user must have a blank
last_name or true must apply (which applies). The query will then
delete all the users in the table. The intruder removed the last quotation mark
by an SQL comment sequence (two hyphens), which cancels everything in the query
till the end of the line.
Smarter attackers can make an injection in any SQL statement,
not only in
Don't worry, the solution is very simple. The problem is caused by several special characters in the variable, such as quotation marks and few others. If we need these characters, we have to escape them by prepending a backslash before them. In our applications, the database driver solves it in some way, either it does it all alone, or we must first escape the data before it's inserted into the query. For sure, check this out before you start working with any database. If you stick to our courses, it's always mentioned there.
The escaped query would look like this:
DELETE FROM `user` WHERE `last'name` = '\' OR 1 --';
Such a query is harmless because the part inserted by the user is considered as text. The quote in the text isn't evaluated as part of the query which makes the comment ignored as well. Another way to protect our application against the injection is to avoid entering variable contents into queries at all. We then use wildcards (question marks) instead in such a query:
DELETE FROM `user` WHERE `last_name` = ?;
And the variables are sent to the database separately, all at once. It inserts the values into the query by itself so that there is no danger. However, this is just theory behind a particular database driver and, as I said, you can find it in the course for particular language, from which you're going to communicate with the database (for example, in the PHP section).
Editing the entries
Databases support 4 basic operations, commonly abbreviated to CRUD (Create, Read, Update, Delete). We already know how to create and delete rows. So we still miss updating and reading. We'll leave searching for the whole next article, but we're going to explain the editing today.
For sure, you'd figure out the editing in phpMyAdmin by yourself, you just
open the table and click Edit next to the given entry. We use the
UPDATE command to edit entries, editing one of the users could look
UPDATE `user` SET `last_name` = 'Jackson', `articles_count` = `articles_count` + 1 WHERE `user_id` = 1;
UPDATE keyword, the name of the table follows. Then,
SET and always the name of the column = value. We can
change the values of multiple columns, we just separate them by commas. We can
even use the previous value from the database and increase it by
as in the example above.
Next time, in the lesson MySQL step by step: Selecting Data (Searching), we'll learn the promised searching.