MCQ On Class 17:
Working with Databases - Part 3
Prepared Statements and SQL Injection Prevention
- What is the primary purpose of prepared statements?
- A) To format SQL queries
- B) To prevent SQL injection attacks
- C) To store SQL queries
- D) To speed up SQL queries
Answer: B) To prevent SQL injection attacks - Which of the following is a benefit of using prepared statements?
- A) Improved security
- B) Easier debugging
- C) Enhanced readability
- D) All of the above
Answer: A) Improved security - In PHP, which function is used to prepare a SQL statement?
- A) mysqli_query()
- B) mysqli_prepare()
- C) mysqli_execute()
- D) mysqli_bind()
Answer: B) mysqli_prepare() - What is SQL injection?
- A) A method of optimizing SQL queries
- B) A security vulnerability
- C) A type of database transaction
- D) A database management tool
Answer: B) A security vulnerability - Which of the following best describes how SQL injection works?
- A) By executing malicious SQL code
- B) By encrypting SQL queries
- C) By compressing SQL data
- D) By altering database structure
Answer: A) By executing malicious SQL code - Which statement is true regarding prepared statements?
- A) They can execute multiple SQL statements at once.
- B) They bind parameters to avoid direct inclusion in the query.
- C) They are less efficient than dynamic SQL queries.
- D) They require no validation of user input.
Answer: B) They bind parameters to avoid direct inclusion in the query. - What does the bind_param function do in PHP prepared statements?
- A) It executes the SQL statement.
- B) It binds variables to placeholders in the SQL query.
- C) It closes the database connection.
- D) It creates a new database connection.
Answer: B) It binds variables to placeholders in the SQL query. - Which placeholder type is used for a string in bind_param?
- A) i
- B) d
- C) s
- D) b
Answer: C) s - What type of attack can be mitigated by using prepared statements?
- A) Cross-Site Scripting (XSS)
- B) Denial of Service (DoS)
- C) SQL Injection
- D) Man-in-the-Middle (MitM)
Answer: C) SQL Injection - Which of the following is NOT a benefit of using prepared statements?
- A) Protection against SQL injection
- B) Easier to maintain code
- C) Improved performance in all cases
- D) Support for parameterized queries
Answer: C) Improved performance in all cases - What is the purpose of mysqli_stmt_execute()?
- A) To fetch results from a prepared statement
- B) To prepare an SQL statement
- C) To execute a prepared SQL statement
- D) To bind parameters to the prepared statement
Answer: C) To execute a prepared SQL statement - Which of the following is a secure way to store passwords in a database?
- A) Plain text
- B) SHA-1 hash
- C) MD5 hash
- D) Password hash with password_hash()
Answer: D) Password hash with password_hash() - What does the mysqli_stmt_bind_param() function do?
- A) Executes the prepared statement.
- B) Binds parameters to the prepared SQL statement.
- C) Closes the prepared statement.
- D) Fetches the results of the statement.
Answer: B) Binds parameters to the prepared SQL statement. - What should you do to prevent SQL injection when accepting user input?
- A) Sanitize input
- B) Use prepared statements
- C) Validate input
- D) All of the above
Answer: D) All of the above - Which of the following PHP functions can be used to establish a database connection?
- A) connect_db()
- B) mysqli_connect()
- C) db_connect()
- D) open_connection()
Answer: B) mysqli_connect() - What is the role of placeholders in prepared statements?
- A) To store SQL queries
- B) To represent values to be bound later
- C) To optimize SQL performance
- D) To prevent all types of attacks
Answer: B) To represent values to be bound later - What is a key difference between prepared statements and regular SQL queries?
- A) Prepared statements are always faster.
- B) Prepared statements are compiled only once.
- C) Regular SQL queries cannot be parameterized.
- D) Regular SQL queries use bind parameters.
Answer: B) Prepared statements are compiled only once. - Which of the following should NOT be used to protect against SQL injection?
- A) Input validation
- B) Prepared statements
- C) Dynamic SQL with user input
- D) Escaping special characters
Answer: C) Dynamic SQL with user input - What does the mysqli_stmt_fetch() function do?
- A) Prepares a SQL statement
- B) Binds parameters to a statement
- C) Fetches the result of a prepared statement
- D) Executes a SQL statement
Answer: C) Fetches the result of a prepared statement - When using prepared statements, what type of SQL commands can be executed?
- A) Only SELECT commands
- B) Only INSERT commands
- C) Any type of SQL command
- D) Only UPDATE and DELETE commands
Answer: C) Any type of SQL command - What is the first step in using prepared statements in PHP?
- A) Bind parameters
- B) Execute the statement
- C) Prepare the SQL statement
- D) Fetch results
Answer: C) Prepare the SQL statement - Which of the following would you use to securely search for users by username?
- A) Directly include user input in the query
- B) Use prepared statements
- C) Sanitize input without prepared statements
- D) Escape special characters in user input
Answer: B) Use prepared statements - What is the correct way to use a prepared statement with a WHERE clause?
- A) SELECT * FROM users WHERE username = ?
- B) SELECT * FROM users WHERE username :username
- C) SELECT * FROM users WHERE username = $username
- D) SELECT * FROM users WHERE username ?
Answer: A) SELECT * FROM users WHERE username = ? - Which PHP function is used to close a prepared statement?
- A) mysqli_close()
- B) mysqli_stmt_close()
- C) close_stmt()
- D) end_statement()
Answer: B) mysqli_stmt_close() - What is an effective method for validating user input in a web application?
- A) Use regex patterns
- B) Trust all user input
- C) Disable form inputs
- D) Avoid any input validation
Answer: A) Use regex patterns - What SQL command is commonly used to retrieve data from a database?
- A) INSERT
- B) UPDATE
- C) SELECT
- D) DELETE
Answer: C) SELECT - What does the PASSWORD_DEFAULT constant do in PHP?
- A) Specifies the hash algorithm to use
- B) Indicates the type of encryption used
- C) Defines the password length
- D) Indicates the default user role
Answer: A) Specifies the hash algorithm to use - What type of parameter is represented by 'i' in bind_param?
- A) String
- B) Integer
- C) Double
- D) Binary
Answer: B) Integer - Which SQL command is used to add a new record to a database?
- A) SELECT
- B) INSERT
- C) UPDATE
- D) DELETE
Answer: B) INSERT - Which method should NOT be relied on to prevent SQL injection?
- A) User input validation
- B) Prepared statements
- C) Encoding output
- D) Dynamic SQL construction
Answer: D) Dynamic SQL construction - What should you do if user input is not properly sanitized?
- A) Ignore it
- B) Log it for review
- C) Use it in SQL queries
- D) Validate and sanitize it
Answer: D) Validate and sanitize it - In SQL, what does the LIKE operator do?
- A) Matches exact values
- B) Compares numeric values
- C) Searches for patterns
- D) Performs mathematical operations
Answer: C) Searches for patterns - What is the default behavior of a SQL query without a WHERE clause?
- A) It only affects a single row.
- B) It retrieves no data.
- C) It affects all rows in the table.
- D) It produces an error.
Answer: C) It affects all rows in the table. - Which of the following is NOT a SQL data type?
- A) VARCHAR
- B) INT
- C) BOOLEAN
- D) CHARACTERISTIC
Answer: D) CHARACTERISTIC - What is the function of htmlspecialchars() in PHP?
- A) Converts special characters to HTML entities
- B) Escapes SQL characters
- C) Cleans input data
- D) Validates form submissions
Answer: A) Converts special characters to HTML entities - What is a common consequence of a successful SQL injection attack?
- A) Database structure changes
- B) Data loss or corruption
- C) Unauthorized data access
- D) All of the above
Answer: D) All of the above - Which SQL command is used to delete records from a database?
- A) REMOVE
- B) DELETE
- C) DROP
- D) TRUNCATE
Answer: B) DELETE - In a database, what is a primary key?
- A) A unique identifier for a record
- B) A column with duplicate values
- C) A reference to another table
- D) An index for faster searching
Answer: A) A unique identifier for a record - What is the purpose of the password_verify() function in PHP?
- A) To hash a password
- B) To verify a hashed password against a plain text password
- C) To store passwords in the database
- D) To create a new user account
Answer: B) To verify a hashed password against a plain text password - What does the SQL command DROP TABLE do?
- A) Deletes all rows in a table
- B) Removes a table from the database
- C) Deletes a specific row
- D) Updates a table structure
Answer: B) Removes a table from the database - Which SQL keyword is used to modify existing records in a database?
- A) MODIFY
- B) UPDATE
- C) CHANGE
- D) ALTER
Answer: B) UPDATE - What is a foreign key in a database?
- A) A key that uniquely identifies a record
- B) A field that links two tables together
- C) A field that contains duplicate values
- D) A temporary key for indexing
Answer: B) A field that links two tables together - Which function would you use to create a hash of a password in PHP?
- A) password_encrypt()
- B) password_hash()
- C) hash_password()
- D) create_hash()
Answer: B) password_hash() - Which SQL command is used to change a table's structure?
- A) CHANGE
- B) ALTER
- C) MODIFY
- D) UPDATE
Answer: B) ALTER - What does the mysqli_fetch_assoc() function return?
- A) An array of all results
- B) The next row in the result set as an associative array
- C) The number of rows in the result set
- D) A boolean value indicating success
Answer: B) The next row in the result set as an associative array - What is a good practice for password management in applications?
- A) Storing passwords in plain text
- B) Using weak hashing algorithms
- C) Salting passwords before hashing
- D) Reusing passwords across multiple sites
Answer: C) Salting passwords before hashing - Which PHP function is used to escape special characters in a string for SQL queries?
- A) escape_string()
- B) mysqli_real_escape_string()
- C) sanitize_string()
- D) safe_string()
Answer: B) mysqli_real_escape_string() - What is the role of a database management system (DBMS)?
- A) To create user interfaces
- B) To manage database operations
- C) To host web applications
- D) To encrypt sensitive data
Answer: B) To manage database operations - In a SQL statement, what does * represent?
- A) All columns
- B) All rows
- C) A specific column
- D) An empty result
Answer: A) All columns - What is the first step in securing a database application?
- A) Use strong passwords
- B) Update software regularly
- C) Validate and sanitize user input
- D) Encrypt sensitive data
Answer: C) Validate and sanitize user input - What does the mysqli_num_rows() function do?
- A) Returns the number of columns in a result set
- B) Returns the number of rows in a result set
- C) Returns a boolean indicating success
- D) Returns the last inserted ID
Answer: B) Returns the number of rows in a result set - Which type of attack is SQL injection categorized under?
- A) Network attack
- B) Web application attack
- C) Phishing attack
- D) Denial of Service attack
Answer: B) Web application attack - What is the correct method to execute a prepared statement?
- A) $stmt->run();
- B) $stmt->execute();
- C) $stmt->start();
- D) $stmt->call();
Answer: B) $stmt->execute(); - What does the term 'sanitization' refer to in web security?
- A) Removing sensitive data
- B) Validating and cleaning input data
- C) Encrypting data
- D) Storing data securely
Answer: B) Validating and cleaning input data - Which type of SQL statement is used to create a new table?
- A) CREATE
- B) INSERT
- C) UPDATE
- D) SELECT
Answer: A) CREATE - What does the GROUP BY clause do in SQL?
- A) Groups rows that have the same values
- B) Sorts the results in ascending order
- C) Filters results based on a condition
- D) Joins multiple tables together
Answer: A) Groups rows that have the same values - Which SQL clause is used to filter records?
- A) WHERE
- B) HAVING
- C) ORDER BY
- D) GROUP BY
Answer: A) WHERE - Which SQL function is used to count the number of rows in a result set?
- A) COUNT()
- B) SUM()
- C) TOTAL()
- D) ROW_COUNT()
Answer: A) COUNT() - What is the purpose of the HAVING clause in SQL?
- A) To filter records before grouping
- B) To filter records after grouping
- C) To sort the results
- D) To join multiple tables
Answer: B) To filter records after grouping - Which of the following is NOT a relational database management system (RDBMS)?
- A) MySQL
- B) PostgreSQL
- C) MongoDB
- D) Oracle
Answer: C) MongoDB - What is the purpose of indexing in databases?
- A) To create relationships between tables
- B) To enhance query performance
- C) To enforce data integrity
- D) To store large amounts of data
Answer: B) To enhance query performance - What is a SQL view?
- A) A temporary table
- B) A stored query
- C) A user-defined function
- D) A type of index
Answer: B) A stored query - In SQL, what is a subquery?
- A) A query that retrieves multiple results
- B) A query within another query
- C) A query that updates data
- D) A query that deletes data
Answer: B) A query within another query - What does the UNION operator do in SQL?
- A) Combines two or more SELECT statements
- B) Joins two tables together
- C) Updates records in multiple tables
- D) Deletes records from multiple tables
Answer: A) Combines two or more SELECT statements - Which of the following is an example of a non-relational database?
- A) SQL Server
- B) PostgreSQL
- C) SQLite
- D) Cassandra
Answer: D) Cassandra - What is the purpose of the JOIN clause in SQL?
- A) To combine rows from two or more tables based on a related column
- B) To create a new table
- C) To delete records from a table
- D) To modify existing records
Answer: A) To combine rows from two or more tables based on a related column - What does the LIMIT clause do in a SQL query?
- A) Restricts the number of rows returned
- B) Limits the size of the database
- C) Defines a maximum value for a column
- D) Controls user access
Answer: A) Restricts the number of rows returned - Which of the following is a secure method to store passwords?
- A) Plain text
- B) Encrypted format
- C) Hashed and salted
- D) None of the above
Answer: C) Hashed and salted - What is a stored procedure?
- A) A collection of SQL statements stored in the database
- B) A command to create a new table
- C) A query that retrieves data
- D) A function that returns a single value
Answer: A) A collection of SQL statements stored in the database - Which SQL statement is used to add a new record to a table?
- A) ADD
- B) INSERT
- C) CREATE
- D) UPDATE
Answer: B) INSERT - What does mysqli_close() do?
- A) Closes the database connection
- B) Deletes all records from a table
- C) Updates the database schema
- D) Executes a SQL command
Answer: A) Closes the database connection - What is the purpose of a transaction in SQL?
- A) To group multiple operations into a single unit
- B) To optimize query performance
- C) To create backups of the database
- D) To enforce security
Answer: A) To group multiple operations into a single unit - What does the ROLLBACK command do in SQL?
- A) Commits a transaction
- B) Undoes a transaction
- C) Saves changes to the database
- D) Deletes a transaction
Answer: B) Undoes a transaction - Which of the following is a best practice for database security?
- A) Use default usernames and passwords
- B) Limit user permissions
- C) Expose error messages to users
- D) Store sensitive data without encryption
Answer: B) Limit user permissions - What is the purpose of the EXPLAIN statement in SQL?
- A) To describe the structure of a table
- B) To provide information about how a SQL query will be executed
- C) To explain error messages
- D) To generate random data
Answer: B) To provide information about how a SQL query will be executed - Which of the following SQL commands will create an index?
- A) CREATE INDEX
- B) ADD INDEX
- C) MAKE INDEX
- D) NEW INDEX
Answer: A) CREATE INDEX - What is the main purpose of data normalization?
- A) To reduce data redundancy
- B) To increase data redundancy
- C) To optimize performance
- D) To enhance security
Answer: A) To reduce data redundancy - Which SQL command is used to modify an existing table?
- A) ALTER TABLE
- B) CHANGE TABLE
- C) UPDATE TABLE
- D) MODIFY TABLE
Answer: A) ALTER TABLE - What is the purpose of GROUP_CONCAT() in SQL?
- A) To count the number of rows in a group
- B) To concatenate values from multiple rows into a single string
- C) To group rows by a specific column
- D) To join multiple tables
Answer: B) To concatenate values from multiple rows into a single string - What is the difference between INNER JOIN and OUTER JOIN?
- A) INNER JOIN returns all records, OUTER JOIN returns matching records only.
- B) INNER JOIN returns matching records, OUTER JOIN returns all records.
- C) Both return the same records.
- D) INNER JOIN is faster than OUTER JOIN.
Answer: B) INNER JOIN returns matching records, OUTER JOIN returns all records. - What is a schema in the context of databases?
- A) The physical storage of data
- B) The blueprint of the database structure
- C) A set of security permissions
- D) A backup of the database
Answer: B) The blueprint of the database structure - Which SQL function is used to find the maximum value in a column?
- A) MAX()
- B) HIGHEST()
- C) TOP()
- D) BIGGEST()
Answer: A) MAX() - What is the purpose of the IN operator in SQL?
- A) To check if a value exists in a list of values
- B) To combine two queries
- C) To create a new table
- D) To filter rows based on a condition
Answer: A) To check if a value exists in a list of values - Which of the following can be a result of failing to validate user input?
- A) Data integrity
- B) Increased security
- C) SQL injection attacks
- D) Improved performance
Answer: C) SQL injection attacks - What is the purpose of the TRUNCATE TABLE statement in SQL?
- A) To delete specific records
- B) To remove all records from a table quickly without logging
- C) To modify the structure of a table
- D) To create a backup of a table
Answer: B) To remove all records from a table quickly without logging - Which SQL keyword is used to sort the results of a query?
- A) ORDER BY
- B) SORT BY
- C) GROUP BY
- D) ARRANGE BY
Answer: A) ORDER BY - What does the term 'data integrity' refer to?
- A) Data is stored securely
- B) Data remains accurate and consistent
- C) Data is accessible to users
- D) Data is encrypted
Answer: B) Data remains accurate and consistent - What is a composite key in a database?
- A) A single key that identifies a record
- B) A combination of two or more columns to identify a record
- C) A temporary key used during transactions
- D) A key that links to another table
Answer: B) A combination of two or more columns to identify a record - What is a trigger in SQL?
- A) A special type of index
- B) A stored procedure that automatically runs when certain events occur
- C) A command to delete records
- D) A method to join tables
Answer: B) A stored procedure that automatically runs when certain events occur - What is the result of executing a SQL command with syntax errors?
- A) The command runs successfully
- B) The command is ignored
- C) An error message is returned
- D) Data is deleted
Answer: C) An error message is returned - What does the SET command do in a SQL UPDATE statement?
- A) Specifies the table to update
- B) Defines the new values for columns
- C) Deletes records
- D) Creates a new record
Answer: B) Defines the new values for columns - What is the purpose of data redundancy in databases?
- A) To improve performance
- B) To increase storage costs
- C) To maintain multiple copies of the same data
- D) None of the above
Answer: A) To improve performance - Which SQL clause is used to specify the condition for filtering records?
- A) WHERE
- B) FILTER
- C) HAVING
- D) SELECT
Answer: A) WHERE - What is the effect of using a wildcard character (%) in SQL?
- A) It matches zero or more characters
- B) It matches a single character
- C) It does not affect the query
- D) It creates a new table
Answer: A) It matches zero or more characters - Which of the following statements is true regarding HAVING and WHERE clauses?
- A) WHERE is used before grouping, while HAVING is used after grouping.
- B) HAVING is used for filtering individual records, while WHERE is used for groups.
- C) Both clauses perform the same function.
- D) WHERE is used for aggregate functions, while HAVING is not.
Answer: A) WHERE is used before grouping, while HAVING is used after grouping. - What does the acronym SQL stand for?
- A) Structured Query Language
- B) Simple Query Language
- C) Sequential Query Language
- D) Standardized Query Language
Answer: A) Structured Query Language - Which of the following is NOT a type of SQL join?
- A) INNER JOIN
- B) OUTER JOIN
- C) CROSS JOIN
- D) ALIAS JOIN
Answer: D) ALIAS JOIN - What is the primary purpose of indexing in a database?
- A) To reduce the size of the database
- B) To speed up data retrieval
- C) To create backups
- D) To enforce security
Answer: B) To speed up data retrieval - Which SQL command is used to change a user's password?
- A) UPDATE
- B) CHANGE
- C) ALTER
- D) MODIFY
Answer: A) UPDATE - What is the purpose of a foreign key in a database? - A) To enforce uniqueness within a table - B) To create a relationship between two tables - C) To store large data objects - D) To speed up queries
Answer: B) To create a relationship between two tables