CCNA Db App Forensics Questions

MCQmedium

Refer to the exhibit. An analyst recovers this binary log entry from a MySQL server. What does the timestamp '190101 10:00:00' represent?

A.The time the DELETE statement was executed on the MySQL server

B.The time the client sent the query to the server

C.The time the binary log file was written to disk

D.The time the transaction was committed

AnswerA

The timestamp in the binary log entry records when the MySQL server executed the DELETE statement, as part of its statement-based or row-based logging. This is the server's authoritative clock at the moment the statement was processed, not when the client sent it or when the file was flushed. MySQL writes this event timestamp into the binary log header for replication and point-in-time recovery, reflecting execution time.

Why this answer

In MySQL binary logs, the timestamp in the 'Query' event header (e.g., '190101 10:00:00') records the server's local time when the statement began executing. This is the time the DELETE statement was actually processed by the MySQL server, not when the client sent it or when the log was written. The binary log captures the exact moment the server starts executing the query, making option A correct.

Exam trap

The CHFI exam often tests the distinction between 'execution time on server' vs 'client send time' or 'commit time', and the trap here is that candidates confuse the binary log event timestamp with the client-side query submission time or the transaction commit time, which are recorded differently in MySQL's binary log format.

How to eliminate wrong answers

Option B is wrong because the timestamp in the binary log event header reflects the server's execution start time, not the client's query send time; client-side timestamps are not recorded in the binary log. Option C is wrong because the binary log file write time is recorded in the file header or as a separate 'Rotate' event, not in the individual query event timestamps. Option D is wrong because the transaction commit time is recorded in a 'Xid' event or 'Query' event with a 'COMMIT' statement, not in the timestamp of a DELETE statement event; the timestamp here marks the start of the statement execution, not the commit.

Practice this question →

MCQmedium

A forensic investigator is examining a compromised database server running Microsoft SQL Server 2019. The attacker gained access and executed several destructive queries. The investigator needs to determine the exact time and text of the malicious queries. The database is configured with the full recovery model, and transaction log backups are available. Which of the following should the investigator use to recover the query text?

A.Restore the database from the last full backup, then use SQL Server Profiler to capture live queries as they are re-executed.

B.Use a third-party log reader tool that parses the transaction log and extracts the query text from log records, such as ApexSQL Log or Quest Toad.

C.Use the sys.fn_dblog function to read the active transaction log and filter for LOP_INSERT_ROWS and LOP_DELETE_ROWS operations.

D.Use the sys.dm_exec_query_stats dynamic management view to retrieve the query text and execution statistics.

AnswerB

Third-party log reader tools can parse the transaction log and reconstruct the exact query text from log records, including the time and user. They are designed for forensic analysis of SQL Server logs and can extract detailed information even from inactive log portions, provided the log has not been truncated.

Why this answer

The transaction log in full recovery model records all transactions, but the native sys.fn_dblog function does not directly provide query text. Specialized third-party log reader tools can interpret log records and reconstruct the original queries, including the exact text and timing. This is the most reliable method for forensic recovery of query text from SQL Server transaction logs.

Exam trap

The trap here is assuming that built-in SQL Server functions like sys.fn_dblog provide complete query text without additional parsing.

Practice this question →

MCQhard

You are a forensic investigator responding to an incident at a financial institution. The organization uses Microsoft SQL Server 2016 for its transaction processing system. The database is configured with full recovery model and transaction log backups are taken every 15 minutes. The incident response team has identified that an attacker gained access to the database server via compromised credentials and executed a series of malicious SQL statements, including data exfiltration and deletion of critical records. The time of the attack is estimated to be between 2:00 PM and 2:05 PM. The last full backup was taken at 12:00 AM (midnight) the same day. Transaction log backups are available for the entire day. The last transaction log backup before the attack was taken at 1:45 PM. The next transaction log backup after the attack was taken at 2:15 PM. The database is still online and being used by the business. Management wants to recover the database to a point just before the attack (2:00 PM) to minimize data loss, while preserving evidence for investigation. Which of the following actions should you take FIRST?

A.Perform a tail-log backup of the database using the NORECOVERY option to capture all transactions since the last log backup.

B.Immediately restore the full backup from midnight and all transaction log backups up to 1:45 PM to a separate server for forensic analysis.

C.Shut down the SQL Server service to prevent further changes and then restore the database from backup.

D.Restore the database to a point in time using the full backup and all transaction log backups up to 1:45 PM, then apply the 2:15 PM backup to recover lost data.

AnswerA

Performing a tail-log backup with NORECOVERY captures every transaction that was recorded in the active portion of the transaction log after the last full transaction log backup, including transactions in flight or not yet backed up. The NORECOVERY option transitions the database into the Restoring state, preserving the current transaction log as a backup file that can be used for point-in-time recovery. This is the only way to preserve the complete post-backup forensic evidence, and it must be done before any restore operation is attempted.

Why this answer

Performing a tail-log backup with NORECOVERY captures all transactions committed after the last log backup (1:45 PM) up to the current point in time, including the attack period. This preserves the database in a restoring state, preventing further changes while allowing point-in-time recovery to just before 2:00 PM. It is the mandatory first step to minimize data loss and maintain forensic integrity before any restore operations.

Exam trap

The CHFI exam often tests the misconception that you should immediately restore from the last known good backup or shut down the server, when the correct first action is always to secure the current transaction log via a tail-log backup to capture all recent changes and enable precise point-in-time recovery.

How to eliminate wrong answers

Option B is wrong because restoring backups to a separate server for forensic analysis is a valid subsequent step, but it should not be performed first; the immediate priority is to capture the tail of the transaction log from the live database to avoid losing transactions that occurred after the last log backup. Option C is wrong because shutting down the SQL Server service would abruptly terminate the database and could corrupt the transaction log, potentially losing the tail-log data needed for point-in-time recovery; a controlled tail-log backup is required instead. Option D is wrong because applying the 2:15 PM backup would include the attacker's malicious transactions and deletions, which would reintroduce the compromised data and fail to achieve recovery to just before the attack.

Practice this question →

MCQhard

An organization uses Microsoft SQL Server 2019 with full recovery model. A database administrator accidentally executed a DROP TABLE statement. The transaction log was backed up immediately after the incident. Which forensic technique would allow the analyst to restore the dropped table?

A.Restore the transaction log backup taken after the DROP TABLE and apply it to the database.

B.Use the RESTORE LOG statement with the NO_TRUNCATE option to recover the table.

C.Perform a tail-log backup, then restore the full backup and all subsequent transaction log backups, stopping before the DROP TABLE.

D.Restore the most recent full backup and ignore subsequent transaction log backups.

AnswerC

The correct procedure is to first back up the tail of the transaction log to capture all log records generated since the last backup, including the DROP TABLE transaction. Then restore the most recent full backup in NORECOVERY mode, followed by every subsequent transaction log backup using STOPAT (or STOPBEFOREMARK) set to a time just before the drop. This rolls the database forward to the pre-drop state while preserving all earlier committed changes.

Why this answer

Under the full recovery model, point-in-time recovery is required to undo the DROP TABLE. By performing a tail-log backup (to capture any transactions after the last log backup), then restoring the full backup and all subsequent transaction log backups with STOPAT or STOPBEFOREMARK to the moment just before the DROP TABLE, the analyst can recover the table without losing other transactions. This is the only method that preserves the dropped table's data while maintaining database consistency.

Exam trap

The trap here is that candidates often think a simple transaction log restore (Option A) or a full backup restore (Option D) will suffice, failing to recognize that point-in-time recovery with a tail-log backup and STOPAT is required to skip the destructive DDL statement.

How to eliminate wrong answers

Option A is wrong because restoring only the transaction log backup taken after the DROP TABLE would apply the DROP TABLE operation again, permanently removing the table. Option B is wrong because the NO_TRUNCATE option is used to back up a tail of the log when the database is damaged or offline, not to recover a dropped table; it does not provide point-in-time recovery to skip the DROP. Option D is wrong because restoring only the most recent full backup would lose all changes made after that backup, including the data that existed before the DROP, and would not recover the dropped table.

Practice this question →

MCQmedium

During a database forensic investigation, an analyst recovers a MySQL binary log file (binlog.000012) from a compromised server. Which command should the analyst use to extract the actual SQL statements from this binary log in a human-readable format?

A.mysqldump --binlog binlog.000012

B.mysqlimport --binlog binlog.000012

C.mysqlcheck --binlog binlog.000012

D.mysqlbinlog binlog.000012

AnswerD

mysqlbinlog is the official MySQL utility for reading binary log files and converting their events into human-readable SQL statements or, with appropriate options, into replayable SQL for database restoration. It supports statement-based, row-based, and mixed binlog formats, and it allows selective forensic analysis using time ranges, position ranges, and offset filters. In a database forensic investigation, running mysqlbinlog binlog.000012 reveals the exact transactional operations, timestamps, server IDs, and event sequence recorded in that log, enabling reconstruction of unauthorized changes or data exfiltration attempts.

Why this answer

The `mysqlbinlog` utility is specifically designed to parse MySQL binary log files and output the contained SQL statements in a human-readable format. Binary logs record all data-changing operations (e.g., INSERT, UPDATE, DELETE) in a proprietary binary format, so only `mysqlbinlog` can decode them back into readable SQL for forensic analysis.

Exam trap

EC-Council often tests the distinction between MySQL administrative utilities (mysqldump, mysqlcheck, mysqlimport) and the forensic-specific tool mysqlbinlog, exploiting the common misconception that any MySQL command with 'binlog' in its name can read binary logs.

How to eliminate wrong answers

Option A is wrong because `mysqldump` is a backup tool that exports database schemas and data as SQL text, not a binary log reader; it has no `--binlog` option. Option B is wrong because `mysqlimport` is used to load data from text files into tables using LOAD DATA INFILE, and it does not interact with binary logs. Option C is wrong because `mysqlcheck` is a maintenance utility for checking, repairing, and optimizing tables; it cannot decode binary log files.

Practice this question →

MCQeasy

A forensic investigator is analyzing a Microsoft SQL Server instance that was compromised. The investigator wants to identify all login attempts that failed due to incorrect passwords. Which system function or view should be queried?

A.sys.dm_exec_sessions

B.sys.dm_tran_locks

C.xp_readerrorlog with filter for 'Login failed'

D.sys.dm_exec_requests

AnswerC

The correct approach is to query the SQL Server error log via xp_readerrorlog with a filter for the error message text '%Login failed%'. SQL Server writes failure audit events, including error 18456 with detail such as the login name and reason, to the error log; xp_readerrorlog is an undocumented extended stored procedure that reads the current or archived logs and accepts parameters to filter by text, which makes it effective for forensic analysis of failed logins.

Why this answer

The xp_readerrorlog extended stored procedure reads the SQL Server error log, which records all login attempts, including failures. By filtering for 'Login failed', the investigator can retrieve the exact entries where authentication failed due to incorrect passwords. This is the standard method for auditing failed logins in SQL Server.

Exam trap

EC-Council often tests the misconception that dynamic management views (DMVs) like sys.dm_exec_sessions store historical authentication data, when in fact they only reflect current state, not past events.

How to eliminate wrong answers

Option A is wrong because sys.dm_exec_sessions shows current active sessions, not historical login failures; it only reflects successful connections. Option B is wrong because sys.dm_tran_locks provides information about current lock states and transactions, not authentication events. Option D is wrong because sys.dm_exec_requests displays currently executing requests, not past login attempts or failures.

Practice this question →

MCQeasy

A forensic investigator is examining a MySQL database server that was compromised. The investigator needs to determine which user account was used to perform unauthorized modifications to a critical table. The MySQL server has the general query log enabled. Which of the following should the investigator review to find the user account associated with the modifications?

A.The MySQL slow query log

B.The MySQL general query log

C.The MySQL error log

D.The MySQL binary log

AnswerB

The MySQL general query log records all SQL statements received from clients, along with the user account and connection details. By reviewing this log, the investigator can identify the exact queries that modified the table and the user account that executed them. This is the correct source for this information.

Why this answer

The MySQL general query log captures all SQL statements along with the user account and connection information. This makes it the ideal source for determining which user executed specific modifications. Other logs like the error log, binary log, and slow query log do not provide the user account context needed for this forensic task.

Exam trap

The trap here is assuming that the binary log contains user information because it records data changes.

Practice this question →

MCQmedium

During a forensic investigation of a MongoDB database, the analyst needs to identify which user executed a particular write operation. Which MongoDB log or feature should the analyst examine?

A.Journal (journal directory)

B.System log (mongod.log)

C.Audit log (auditLog)

D.Oplog (local.oplog.rs)

AnswerC

The audit log (auditLog) is the authoritative forensic source because MongoDB Enterprise's auditing feature, when enabled, emits structured events for user actions including authentication, schema changes, and select CRUD operations. Each audit record contains critical metadata such as the authenticated user (authUser), the exact timestamp, the operation type, and the source IP, enabling precise attribution. Its behavior can be tuned with auditFilter and operationTypes to capture the full scope of actions, making it indispensable for identifying what a user did within the database.

Why this answer

The audit log (auditLog) is the correct source because it is specifically designed to record user authentication and database operations, including which user executed a write operation. MongoDB's audit system captures detailed events such as insert, update, and delete commands along with the authenticated user identity, making it the definitive forensic artifact for user attribution.

Exam trap

EC-Council often tests the misconception that the oplog or system log records user identity, when in fact only the audit log provides authenticated user attribution for database operations.

How to eliminate wrong answers

Option A is wrong because the journal (journal directory) records write-ahead redo logs for crash recovery and durability, not user identity or operation attribution. Option B is wrong because the system log (mongod.log) contains operational messages and errors but does not reliably capture per-operation user context or detailed write command attribution. Option D is wrong because the oplog (local.oplog.rs) is a capped collection used for replication tracking and contains operation details but does not include the authenticated user who executed the write.

Practice this question →

MCQhard

Refer to the exhibit. A database administrator finds the above error log entries when attempting to start the MySQL service. The server was working fine yesterday. What is the most likely cause of this issue?

A.The MySQL user does not have write permissions to the data directory.

B.The binary log is full and cannot be rotated.

C.The server ran out of memory due to high innodb_buffer_pool_size.

D.The InnoDB system tablespace file (ibdata1) is corrupted.

AnswerD

The InnoDB system tablespace file (ibdata1) holds the data dictionary, rollback segments, and undo tablespaces; its first page contains a header that InnoDB validates at startup. If that header or any critical internal page is corrupted, InnoDB cannot initialize its storage engine and aborts with errors such as 'Database page corruption' or 'Cannot open datafile'. This matches the administrator's exhibit, making corruption of ibdata1 the correct explanation; recovery requires restoring the tablespace from backup or rebuilding it with new setup.

Why this answer

The error log entries indicate that InnoDB is unable to open or read the system tablespace file (ibdata1), which is the core file storing the InnoDB data dictionary, undo logs, and doublewrite buffer. A corrupted ibdata1 prevents MySQL from starting because the storage engine cannot initialize its internal structures, even if the server was operational the previous day. This matches the symptom of a sudden failure without prior configuration changes.

Exam trap

EC-Council often tests the distinction between permission errors, disk-full errors, memory errors, and corruption errors, so the trap here is that candidates may confuse a 'cannot start' error with a permission issue or memory exhaustion, rather than recognizing the specific InnoDB corruption signature in the log.

How to eliminate wrong answers

Option A is wrong because if the MySQL user lacked write permissions to the data directory, the error would typically be 'Permission denied' or 'Can't create/write to file', not a corruption-related InnoDB error about ibdata1. Option B is wrong because a full binary log that cannot be rotated would cause a 'Binary log disk full' or 'Could not write to binlog' error, not an InnoDB system tablespace corruption error. Option C is wrong because running out of memory due to high innodb_buffer_pool_size would manifest as an out-of-memory (OOM) kill or allocation failure, not a specific corruption error for ibdata1.

Practice this question →