Understanding Database Concepts
If you’re planning to take the SY0-501 Security+ exam, you should have a basic understanding of database concepts and several of the secure coding techniques and attacks that apply directly to databases.
For example, can you answer this question?
Q. Database administrators have created a database used by a web application. However, testing shows that the application is taking a significant amount of time accessing data within the database. Which of the following actions is MOST likely to improve the overall performance of a database?
A. Normalization
B. Client-side input validation
C. Server-side input validation
D. Obfuscation
More, do you know why the correct answer is correct and the incorrect answers are incorrect? The answer and explanation is available at the end of this post.
Many attacks target server applications such as those hosted on web servers. Web servers are highly susceptible to several types of attacks, such as buffer overflow attacks and SQL injection attacks, because they commonly accept data from users. Other servers are susceptible to some types of command injection attacks.
Database Concepts
Several of the secure coding techniques and attacks apply directly to databases. SQL (pronounced as “sequel” or “es-que-el”) is a Structured Query Language used to communicate with databases. SQL statements read, insert, update, and delete data to and from a database. Many web sites use SQL statements to interact with a database, providing users with dynamic content.
A database is a structured set of data. It typically includes multiple tables and each table holds multiple columns and rows. As an example, consider the figure. It shows the database schema for a database intended to hold information about books and their authors. It includes two incorrect entries, which are described in this blog.
Database schema
The Book table (on the left) identifies the column names for the table. Each of these columns has a name and identifies the data type or attribute type allowed in the column. For example, INT represents integer, VARCHAR represents a variable number of alphanumeric characters, TEXT is used for paragraphs, and decimal can store monetary values.
The Author table holds information on authors, such as their names and addresses. The BookAuthor table creates a relationship between the Book table and the Author table. The Publisher column should not be there, but it helps describe normalization in the next section.
The figure shows three rows of the Author table. It also shows the difference between columns and rows. Because the column identifies the data type, columns are sometimes referred to as attributes. Also, because each row represents a record, rows are sometimes called records or tuples.
Database table
Individual elements within a database are called fields. For example, the field in the second row of the FirstName column is a field holding the value of Moe.
Full Security+ Course
SY0-401 Full Security+ Course
Helping you Pass the First Time
Online access includes all of the content from the
CompTIA Security+: Get Certified Get Ahead: SY0-401 Study Guide
Click here if you're looking for SYO-501 Full Security+ Course
- Introduction
- About the exam (including types of questions and strategies for performance-based questions)
- 100 question pre-assessment exam
- Mastering Security Basics (full content from Chapter 1 of the study guide including the exam topic review and 20 practice test questions)
- Exploring Control Types and Methods (full content from Chapter 2 of the study guide including the exam topic review and 20 practice test questions)
- Understanding Basic Network Security (full content from Chapter 3 of the study guide including the exam topic review and 20 practice test questions)
- Securing Your Network (full content from Chapter 4 of the study guide including the exam topic review and 20 practice test questions)
- Securing Hosts and Data (full content from Chapter 5 of the study guide including the exam topic review and 20 practice test questions)
- Understanding Malware and Social Engineering (full content from Chapter 6 of the study guide including the exam topic review and 20 practice test questions)
- Identifying Advanced Attacks (full content from Chapter 7 of the study guide including the exam topic review and 20 practice test questions)
- Managing Risk (full content from Chapter 8 of the study guide including the exam topic review and 20 practice test questions)
- Preparing for Business Continuity (full content from Chapter 9 of the study guide including the exam topic review and 20 practice test questions)
- Understanding Cryptography (full content from Chapter 10 of the study guide including the exam topic review and 20 practice test questions)
- Exploring Operational Security (full content from Chapter 11 of the study guide including the exam topic review and 20 practice test questions)
- 100 question post-assessment exam
- Security+ Acronyms
Get the SY0-401 Full Security+ Course Here
Test your readiness with these quality materials
Random 100-question tests
Random practice tests from the all of the practice test questions in the CompTIA Security+: Get Certified Get Ahead: SY0-401 Study Guide. All questions include explanations so you'll know why the correct answers are correct, and why the incorrect answers are incorrect.34 Simulated Performance-based Questions
Eight sets of performance-based questions with multiple questions in each set. These questions help you understand and prepare for performance based questions.22 Realistic Performance-based Questions
Two new sets of performance-based questions with a total of 22 questions. These new questions use a new testing engine that includes realistic drag and drop, matching, sorting, and fill in the blank questions.
Flashcard Set
- 273 Security+ Flashcards to reinforce key testable concepts
- 280 Security+ acronyms flashcards to help you master the required acronyms
- 204 Security+ Remember This slides
Audio - SY0-401 Security+ Remember This Audio Files
Learn by Listening. Over one hour and 15 minutes of audio (MP3 downloads.)Audio - SY0-401 Security+ Question and Answer Audio Files
Learn by Listening. Over three hours hour and 15 minutes of audio (MP3 downloads.)Bonus #1
Audio from the end of chapter reviews from each of the chapters in the CompTIA Security+: Get Certified Get Ahead: SY0-401 Study Guide
. Over one hour and 15 minutes of additional audio.Bonus #2
Subnetting mini-tutorial that will help you answer two key question types:- Identify how many hosts a subnet supports
- Identify valid IP addresses within a subnet
Bonus #3
Access the study materials for a total of 60 days because sometimes life happens.Get the SY0-401 Full Security+ Course Here
Click here if you're looking for SYO-501 Full Security+ Course
Normalization
Normalization of a database refers to organizing the tables and columns to reduce redundant data and improve overall database performance. Although there are several normal forms, the first three are the most important.
First Normal Form
A database is in first normal form (1NF) if it meets the following three criteria:
- Each row within a table is unique and identified with a primary key.
- Related data is contained in a separate table.
- None of the columns include repeating groups.
Second Normal Form
Second normal form (2NF) only applies to tables that have a composite primary key, where two or more columns make up the full primary key. The BookAuthor table has a composite key that includes the Book_BookID column and the Author_AuthorID column. A database is in 2NF if it meets the following criteria:
- It is in 1NF.
- Non-primary key attributes are completely dependent on the composite primary key. If any column is dependent on only one column of the composite key, it is not in in 1NF.
The BookAuthor table shown in the figure violates this with the Publisher column. A book has a unique publisher so the publisher is related to the Book_BookID column. However, an author can publish books through multiple publishers, so the publisher value is not dependent on the Author_AuthorID column.
Notice that the Book table correctly has the Publisher column, so the easy fix to have this database in 2NF is to delete the Publisher column in the BookAuthor table.
CompTIA Security+ Study Guide
The 501 Version of the Study Guide is Now Available. Click Here.
The 401 Version of the Study Guide
The CompTIA Security+: Get Certified Get Ahead: SY0-401 Study Guide
is an update to the top-selling SY0-201 and SY0-301 study guides, which have helped thousands of readers pass the exam the first time they took it.
After a comprehensive review by ProCert Labs, the SY0-401 version has been certified as CompTIA Approved Quality Content (CAQC) and covers every aspect of the SY0-401 exam.
It includes the same elements readers raved about in the previous two versions.
Each of the eleven chapters presents topics in an easy to understand manner and includes real-world examples of security principles in action.
You’ll understand the important and relevant security topics for the Security+ exam, without being overloaded with unnecessary details. Additionally, each chapter includes a comprehensive review section to help you focus on what’s important.
Click for Free Preview
Over 400 realistic practice test questions with in-depth explanations will help you test your comprehension and readiness for the exam. The book includes:
- A 100 question pre-test
- A 100 question post-test
- Practice test questions at the end of every chapter.
Each practice test question includes a detailed explanation to help you understand the content and the reasoning behind the question. You’ll be ready to take and pass the exam the first time you take it.
If you plan to pursue any of the advanced security certifications, this guide will also help you lay a solid foundation of security knowledge. Learn this material, and you’ll be a step ahead for other exams. This SY0-401 study guide is for any IT or security professional interested in advancing in their field, and a must read for anyone striving to master the basics of IT security.
Third Normal Form
Third normal form (3NF) helps eliminate unnecessary redundancies within a database. A database is in 3NF if it meets the following criteria:
- It is in This implies it is also in 1NF.
- All columns that aren’t primary keys are only dependent on the primary In other words, none of the columns in the table are dependent on non-primary key attributes.
The Book table violates the second rule of 3NF with the PublisherCity column. The city where the publisher is located is dependent on the publisher, not the book. Imagine this table had 100 book entries from the same publisher located in Virginia Beach. When entering the data, you’d need to repeatedly enter Virginia Beach for this publisher.
There are two ways to fix this. First, ask if the city is needed. If not, delete the column and the database is now in 3NF. If the city is needed, you can create another table with publisher data. You would then relate the Publisher table with the Book table.
Remember this
Normalization is a process used to optimize databases. While there are several normal forms available, a database is considered normalized when it conforms to the first three normal forms.
Q. Database administrators have created a database used by a web application. However, testing shows that the application is taking a significant amount of time accessing data within the database. Which of the following actions is MOST likely to improve the overall performance of a database?
A. Normalization
B. Client-side input validation
C. Server-side input validation
D. Obfuscation
Answer is A. Normalization techniques organize tables and columns in a database and improve overall database performance. None of the other answers improve the database performance.
Input validation techniques help prevent many types of attacks, and server-side input validation techniques are preferred over client-side techniques.
Obfuscation techniques make the code more difficult to read.
See Chapter 7 of the CompTIA Security+: Get Certified Get Ahead: SY0-501 Study Guide
or
Chapter 7 of the CompTIA Security+: Get Certified Get Ahead: SY0-401 Study Guide
for more information on application attacks.
