Prepared Statements and PDO in PHP: Beginner Quiz Quiz

Explore important fundamentals of using prepared statements and PDO in PHP to enhance database security, prevent SQL injection, and manage database interactions efficiently. This quiz covers key concepts, safe practices, and syntax basics to help reinforce your understanding of secure PHP database techniques.

1. Understanding Prepared Statements

   Which key benefit do prepared statements offer when interacting with a database in PHP?

   1. They increase the size of the database.
   2. They help prevent SQL injection attacks.
   3. They encrypt all database data automatically.
   4. They always make the database run faster, regardless of query type.

   Explanation: Prepared statements help prevent SQL injection attacks by separating SQL code from data, ensuring user input is treated as data and not executable code. They do not directly encrypt database data or automatically speed up every query type, especially with small numbers of queries. While performance can improve for repeated statements, the main focus is security.

2. Creating a PDO Instance

   In PHP, which object is typically used to establish a database connection using PDO?

   1. $connect = new mysqlPdo(...)
   2. $conn = openPDOConnection(...)
   3. $db = new SQLConnect(...)
   4. $pdo = new PDO(...)

   Explanation: The correct way to create a PDO database connection in PHP is by instantiating a PDO object using 'new PDO(...)' syntax. The distractors are incorrect because 'SQLConnect', 'mysqlPdo', and 'openPDOConnection' are not valid or standard PHP classes for this purpose.

3. Preparing a Statement

   Which method is used to prepare an SQL statement for execution with PDO in PHP?

   1. $pdo-u003Eready()
   2. $pdo-u003Eprepare()
   3. $pdo-u003Esetup()
   4. $pdo-u003Estatement()

   Explanation: To prepare a statement with PDO, you use the 'prepare()' method, which returns a prepared statement object. Methods like 'setup()', 'ready()', and 'statement()' are not part of the PDO class and do not exist for this purpose, making them incorrect choices.

4. Binding Parameters

   How can a value be safely passed into a prepared statement in PDO using placeholders?

   1. By binding parameters with ':name' or '?' placeholders
   2. By adding input values at the end of the SQL query
   3. By directly writing user input into the SQL string
   4. By using double quotes instead of single quotes around values

   Explanation: Binding parameters with named (':name') or question mark ('?') placeholders safely incorporates user input into SQL queries, protecting against injections. Directly inserting user input into the SQL string can lead to vulnerabilities. Using different quote types does not address security, and adding values outside the query syntax is incorrect.

5. Executing a Prepared Statement

   Which PDO method is commonly called to run a prepared statement after binding parameters?

   1. $stmt-u003Estart()
   2. $stmt-u003Eexecute()
   3. $stmt-u003Erun()
   4. $stmt-u003Elaunch()

   Explanation: After preparing and binding parameters, the 'execute()' method is used to run the statement in PDO. The other options, such as 'run()', 'start()', and 'launch()', are not PDO methods and will not execute a prepared statement.

6. Fetching Results

   Which method retrieves the next row from a result set in PDO after executing a SELECT statement?

   1. $stmt-u003EgetRow()
   2. $stmt-u003Efetch()
   3. $stmt-u003Eextract()
   4. $stmt-u003Eread()

   Explanation: The 'fetch()' method retrieves the next row from the result set after executing a SELECT query with PDO. 'getRow()', 'read()', and 'extract()' are not standard PDO methods and do not fetch rows from a PDO result set.

7. Using Prepared Statements Multiple Times

   What is one advantage of using prepared statements for multiple inserts in a loop in PHP?

   1. The statement is parsed once and executed many times with different values.
   2. They cannot be reused and must be created each time.
   3. Prepared statements always lock the table during execution.
   4. Each statement is recompiled every time.

   Explanation: Prepared statements are parsed and compiled once, then can be executed repeatedly with different values, making them efficient for loops. Recompiling each time is not efficient and untrue. Prepared statements are reusable, and they do not inherently lock tables during execution.

8. Error Handling in PDO

   Which PDO method can retrieve information about the last error that occurred?

   1. $pdo-u003EerrorMessage()
   2. $pdo-u003ElastError()
   3. $pdo-u003EerrorInfo()
   4. $pdo-u003EgetError()

   Explanation: The 'errorInfo()' method provides details about the last error in a PDO connection. The distractors 'lastError()', 'getError()', and 'errorMessage()' are not actual PDO methods and thus cannot be used for this purpose.

9. Closing a PDO Connection

   What is the recommended way to properly close a PDO connection in PHP?

   1. Running $pdo-u003Edisconnect()
   2. Setting the PDO object to null
   3. Calling $pdo-u003Eclose()
   4. Using $pdo-u003Eshutdown()

   Explanation: To close a PDO connection, you assign the object to null, which releases the connection. There is no 'close()', 'disconnect()', or 'shutdown()' method in the PDO class, making these incorrect choices.

10. Using Fetch Modes

    Which fetch mode should you use with PDO to retrieve results as associative arrays?

    1. PDO::FETCH_OBJ
    2. PDO::FETCH_ASSOC
    3. PDO::FETCH_NUMERIC
    4. PDO::FETCH_COLUMN

    Explanation: The 'PDO::FETCH_ASSOC' mode retrieves results as associative arrays, which is useful for referencing columns by name. 'PDO::FETCH_NUMERIC' returns numeric-indexed arrays, 'PDO::FETCH_OBJ' returns results as objects, and 'PDO::FETCH_COLUMN' fetches only a single column from the next row.