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 user_id 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. To 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:

Deleting entries

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 command:

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 in the 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 AND and OR operators:

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

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 text:

Smith

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 --';

Because 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 DELETE.

Solution

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 like this:

UPDATE `user` SET `last_name` = 'Jackson', `articles_count` = `articles_count` + 1 WHERE `user_id` = 1;

After the UPDATE keyword, the name of the table follows. Then, there's 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 1 as in the example above.

Next time, in the lesson MySQL step by step: Selecting Data (Searching), we'll learn the promised searching.