Why SQL security awareness matters - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why is SQL injection a serious security risk?

Imagine a website where users enter their username and password. If the website's database queries are not secure, what can happen if someone uses SQL injection?

A The database will delete itself to prevent attacks.

B The website will automatically block the attacker without any damage.

C The attacker can only see public information, no harm done.

D The attacker can access or modify data they shouldn't be able to see.

💡 Hint

Think about what happens when user input is treated as code.

SQL injection allows attackers to run harmful commands on the database, leading to unauthorized data access or changes.

❓ query_result

intermediate

What happens if user input is not sanitized in this query?

Consider this SQL query used in a login system:

SELECT * FROM users WHERE username = '{user_input}' AND password = '{user_input_password}';

If a user inputs ' OR '1'='1' -- as username and anything as password, what will the query return?

A It returns all users because the condition always becomes true.

B It returns no users because the syntax is invalid.

C It returns only the user with username ' OR '1'='1.

D It causes the database to crash.

💡 Hint

Look at how the OR condition affects the WHERE clause.

The injected OR condition makes the WHERE clause always true, so all rows are returned, bypassing login checks.

📝 Syntax

advanced

Identify the syntax error in this SQL command

Find the syntax error in the following SQL statement:

INSERT INTO users (username, password VALUES ('alice', 'pass123');

A Missing closing parenthesis after column names.

B Incorrect keyword 'VALUES' used.

C Missing comma between values.

D Quotes around values are not allowed.

💡 Hint

Check the parentheses around the column list.

The opening parenthesis after column names is not closed before VALUES keyword, causing syntax error.

❓ optimization

advanced

Which index improves security and performance for user login?

You have a users table with columns: id, username, password_hash, email.

To speed up login checks and prevent unauthorized data access, which index is best to create?

A CREATE INDEX idx_password ON users(password_hash);

B CREATE INDEX idx_username ON users(username);

C CREATE INDEX idx_email ON users(email);

D CREATE INDEX idx_id ON users(id);

💡 Hint

Think about which column is used to find users during login.

Indexing username speeds up lookups and helps enforce uniqueness, reducing risk of unauthorized access.

🔧 Debug

expert

Why does this SQL query expose sensitive data?

Given this query:

SELECT username, password FROM users WHERE active = 1;

Why is this a security risk?

A It lacks a LIMIT clause, causing performance issues.

B It filters only active users, so no risk exists.

C It exposes password hashes which should be kept secret.

D It uses SELECT instead of DELETE, causing data leaks.

💡 Hint

Consider what data should never be exposed in queries.

Passwords or their hashes should never be returned in queries to prevent attackers from stealing them.

Practice

1. Why is it important to be aware of SQL security when managing a database?

easy

A. To protect sensitive data from unauthorized access

B. To make queries run faster

C. To reduce the size of the database

D. To increase the number of users who can access the database

Solution

1. Step 1: Understand the purpose of SQL security

   SQL security is designed to protect data from unauthorized users who might misuse or steal it.

2. Step 2: Identify the correct reason for security awareness

   Protecting sensitive data is the main goal, not improving speed or increasing users.

3. Final Answer:

   To protect sensitive data from unauthorized access -> Option A

4. Quick Check:

   SQL security = protect data [OK]

Hint: Security means protecting data from wrong users [OK]

Common Mistakes:

• Confusing security with performance
• Thinking security increases user count
• Believing security reduces database size

2. Which SQL command is used to give a user permission to access a database object?

easy

A. REVOKE

B. GRANT

C. DELETE

D. SELECT

Solution

1. Step 1: Recall SQL commands for permissions

   GRANT is used to give permissions, REVOKE removes them.

2. Step 2: Identify the command that gives access

   GRANT allows users to access or modify database objects.

3. Final Answer:

   GRANT -> Option B

4. Quick Check:

   GRANT = give permission [OK]

Hint: GRANT gives permission, REVOKE takes it away [OK]

Common Mistakes:

• Confusing GRANT with REVOKE
• Using DELETE or SELECT as permission commands
• Thinking SELECT grants permissions

3. Consider this SQL command:
REVOKE UPDATE ON employees FROM user_john;
What is the effect of this command?

medium

A. User John is granted update permission on employees

B. User John can update the employees table

C. User John is deleted from the database

D. User John can no longer update the employees table

Solution

1. Step 1: Understand the REVOKE command

   REVOKE removes permissions previously given to a user.

2. Step 2: Apply to the given command

   This command removes the UPDATE permission on the employees table from user_john.

3. Final Answer:

   User John can no longer update the employees table -> Option D

4. Quick Check:

   REVOKE UPDATE = remove update rights [OK]

Hint: REVOKE removes permission, so user loses update rights [OK]

Common Mistakes:

• Thinking REVOKE grants permission
• Confusing REVOKE with DELETE user
• Assuming REVOKE affects all permissions

4. You run this SQL command:
GRANT SELECT, INSERT employees TO user_anna;
But it returns an error. What is the likely problem?

medium

A. The keyword TO is missing

B. The table name is incorrect

C. The keyword ON is missing before the table name

D. User_anna does not exist

Solution

1. Step 1: Check correct GRANT syntax

   The correct syntax is: GRANT privileges ON table TO user;

2. Step 2: Identify missing keyword

   The command misses ON before the table name employees.

3. Final Answer:

   The keyword ON is missing before the table name -> Option C

4. Quick Check:

   GRANT syntax requires ON before table [OK]

Hint: GRANT needs ON before table name [OK]

Common Mistakes:

• Omitting ON keyword
• Confusing TO placement
• Assuming user existence causes syntax error

5. A database admin wants to ensure that a user can only read data but not change it. Which SQL command should they use?

hard

A. GRANT SELECT ON database TO user_name;

B. GRANT UPDATE ON database TO user_name;

C. REVOKE SELECT ON database FROM user_name;

D. GRANT DELETE ON database TO user_name;

Solution

1. Step 1: Identify read-only permission

   SELECT permission allows reading data without changing it.

2. Step 2: Choose the command granting only read access

   GRANT SELECT ON database TO user_name; gives read-only access.

3. Final Answer:

   GRANT SELECT ON database TO user_name; -> Option A

4. Quick Check:

   SELECT = read-only permission [OK]

Hint: Use GRANT SELECT for read-only access [OK]

Common Mistakes:

• Granting UPDATE or DELETE instead of SELECT
• Revoking SELECT instead of granting
• Granting all permissions accidentally