Database and Application Forensics practice questions

Restore the transaction log files from backup and mount them to recover the deleted rows.

Why wrong: Restoring InnoDB's transaction (redo) log files and mounting them is a misconception: the redo log (ib_logfile*) records physical page changes for crash recovery, not logical SQL operations. It is a cyclic, overwritten buffer that cannot be queried or selectively replayed to resurrect deleted rows. Deleted-row recovery from redo logs is not possible; the binary log is the component designed to record logical or row-based changes for point-in-time recovery.

Use the 'SHOW UNDO' command to retrieve the deleted rows from undo tablespace.

Why wrong: MySQL has no 'SHOW UNDO' command, so this option is invalid as a direct SQL operation. The undo tablespace does contain the pre-image of rows for active transactions (to support MVCC and ROLLBACK), but those records are not exposed through any query interface and are subject to purging by the purge thread once the transaction commits and no older read view depends on them. Even with a low-level parser, undo records only exist while the undo slot is not reused, making them an unreliable, non-supported forensic source.

Query the information_schema database to retrieve deleted rows from the data dictionary.

Why wrong: The information_schema database is a virtual schema that exposes metadata—table names, column definitions, constraints, and status variables—from the data dictionary and server internals. It contains no user row data, much less historical versions of deleted rows. Deleted rows are not stored in the data dictionary; the data dictionary stores structural metadata, so querying information_schema for lost data is categorically incorrect.

Control file

Why wrong: The control file is a small binary metadata file that defines the database's physical structure, including the database name, datafile locations, redo log file locations, and the current system change number (SCN). It contains no record of SQL statements, user sessions, or application-level activity, so it would be completely irrelevant to a forensic investigation seeking the actual SQL commands issued by a compromised web application.

Redo log files

Why wrong: Redo log files record physical changes made to data blocks, storing before and after images of modified data in a proprietary binary format. They do not contain the originating SQL text that produced those changes, and while they can be analyzed with tools like LogMiner to approximate operations, they cannot definitively reveal the exact SQL statement or the application context, making them unsuitable for identifying the specific injection payload or query sent by the attacker.

Alert log (alert_SID.log)

Why wrong: The alert log (alert_SID.log) is a chronological record of major database events such as startup/shutdown, redo log switches, ORA error messages, and recovery actions. It never stores user SQL statements or application transactions, so while it may reveal side-effects of an attack like unexpected shutdowns or internal errors, it cannot provide the exact SQL text used in the compromise and is therefore not a source for the queries themselves.

Journal (journal directory)

Why wrong: The MongoDB journal (journal directory) is a write-ahead redo log used exclusively for crash recovery, ensuring that committed writes survive an unclean shutdown by replaying the last checkpoint. Its records contain physical data-file changes and transaction identifiers, not the authenticated user, client IP, or logical operation that produced them. The journal is also truncated or deleted on clean shutdown, so it lacks the historical depth and attribution metadata required for forensic investigation of user actions.

System log (mongod.log)

Why wrong: The system log (mongod.log) captures server-level operational messages such as startup/shutdown, connection lifecycle, network errors, and severe database exceptions, with verbosity set by the administrator. It does not systematically record every CRUD operation or bind that operation to a specific authenticated user; even when profiling is enabled, the log entries lack structured fields like username, session ID, and full audit event types. Therefore, while the system log may help timeline reconstruction, it is not a reliable source for determining who performed a particular action.

Oplog (local.oplog.rs)

Why wrong: The oplog (local.oplog.rs) is a capped collection used internally for replication, recording each write operation on the primary so secondaries can apply the same changes. Its documents contain the operation details (like database, collection, and update documents) but do not include the originating user, session token, or client address, because replication is mostly operation-based rather than user-based. Additionally, the oplog is a fixed-size circular buffer that continuously overwrites older entries, so historical data is lost quickly and cannot serve as a durable audit trail for user actions.

Review the pg_stat_activity view to see the history of queries executed.

Why wrong: pg_stat_activity is a live system view that exposes only current backend sessions; its query column contains the statement currently executing (or the last statement in an idle-in-transaction session), not a historical log of every query run. Once a DELETE transaction completes, the view no longer retains that statement text, and if the backend disconnects, the row disappears entirely, so it cannot reconstruct a deletion that already occurred on the server.

Examine the archive_status directory to find the timestamp of the WAL file that contains the deletion.

Why wrong: The archive_status directory inside pg_wal contains only marker files with names like 000000010000000000000001.ready or .done, which indicate whether a WAL segment has been selected for archiving or successfully archived. These marker files are effectively empty and their filesystem timestamps reflect archiving activity, not the internal database operations recorded inside the WAL segments, so browsing this directory cannot reveal the timestamp or even the existence of a specific DELETE statement.

Query the pg_audit table to retrieve a log of all DELETE statements.

Why wrong: PostgreSQL has no built-in pg_audit table; pgAudit is a separate extension that must be explicitly installed and enabled by adding pgaudit to shared_preload_libraries and setting pgaudit.log in postgresql.conf. Even then, pgAudit writes its output to the PostgreSQL server log as text, not to a relational table, so a query such as SELECT * FROM pg_audit would fail with a relation-does-not-exist error unless a custom table was manually created and populated by a logging process.

Extract the file system journal

Why wrong: The file system journal, such as the ext4 journal or NTFS $LogFile, is a persistent metadata structure stored on the disk volume to enable crash recovery and rollback. It records filesystem transactions in dedicated on-disk blocks, not in RAM, so its contents survive a reboot and remain available after power-off. Extracting it therefore preserves non-volatile data and is not a method for collecting volatile evidence.

Take a backup of the database using mysqldump

Why wrong: mysqldump produces a logical backup by reading table contents from the database's disk-backed storage and emitting SQL statements, typically while the server is running. The backup artifacts, including the InnoDB redo logs and table data, reside on persistent media, so the resulting dump represents data at rest rather than transient runtime state. Since the data is recoverable from the hard drive after power loss, this is non-volatile collection and does not satisfy the requirement to preserve volatile artifacts.

Create a forensic image of the hard disk

Why wrong: Creating a forensic image of the hard disk generates a bit-for-bit copy of the underlying storage media, capturing allocated and unallocated space of partitions and filesystems. Disk media are non-volatile because they retain data without electrical power, and the imaging process reads only storage blocks, not system memory or kernel structures. This technique is appropriate for analyzing persistent evidence, but it is not a valid method for collecting volatile data that only exists while the machine is powered on.

Restore the database from the latest backup to ensure data integrity

Why wrong: Restoring from backup is a remediation step, not a forensic one: it overwrites the live database files and wipes the very artifacts you need — including binary logs, current tablespaces, and any malicious database objects — with a pre-incident state. The backup is at best a known-good baseline, and restoring it on the original host destroys the evidence necessary for a timeline, so imaging or logical extraction of the live instance must happen first.

Immediately change all database user passwords to prevent further access

Why wrong: Immediately rotating passwords mutates the authentication metadata, invalidates active sessions, and erases evidence of the attacker's legitimate access paths, while also alerting the intruder via failed logins after their session expires. It can destroy proof of account compromise and may not even stop an attacker with alternate persistence such as UDFs or lateral movement; credential changes belong in the containment phase, after forensically sound data collection, not before.

The time the client sent the query to the server

Why wrong: The timestamp reflects server-side processing, not the client's send time. Network latency, client-side batching, or queuing can delay the query arrival; the binary log captures when the server actually started executing the statement, after receiving it. So it cannot be used to measure client behavior or network transmission time.

The time the binary log file was written to disk

Why wrong: The binary log file is written as events are generated, but the event timestamp is set at execution time, independent of when the log file is physically flushed to disk. Disk I/O, buffering, and sync settings may cause a delay between event generation and file write. Therefore the timestamp does not indicate the write time to storage.

The time the transaction was committed

Why wrong: For a DELETE statement, particularly in non-transactional storage engines like MyISAM or with autocommit, there is no explicit transaction commit; the statement is immediately durable. Even in InnoDB, the binary log records the statement execution time, not the commit time, as a commit may occur later at the end of the transaction. Thus the timestamp is not about commit timing.

Run DBCC CHECKDB to verify database consistency

Why wrong: DBCC CHECKDB is a consistency and repair utility that scans allocation, index, and page integrity rather than preserving transactional history. It does not fill gaps in the log or expose log records, and using a repair option can actually modify the database, so it neither acquires evidence nor safely answers a forensic question.

Restart the SQL Server service to flush pending transactions

Why wrong: Restarting the SQL Server service forces a crash-recovery sequence that rolls committed transactions forward and uncommitted transactions back, and any log records still only in the in-memory log buffer may be lost rather than flushed. The restart itself changes the runtime state, invalidates volatile evidence, and can make later analysis of the original transaction stream impossible, so it should never be used as an acquisition technique.

Truncate the transaction log to free space

Why wrong: Truncating the transaction log marks inactive log records as reusable and can purge the very information needed to prove what transactions occurred and who executed them. This destroys potential evidence before it has been captured, which is exactly why a forensic examiner must never run BACKUP LOG WITH TRUNCATE_ONLY or DBCC SHRINKFILE to clear space during an investigation.

The transaction has been rolled back

Why wrong: A rolled-back transaction is removed from V$TRANSACTION as soon as the rollback completes; at the moment this query ran, the row still exists and its STATUS column displays 'ACTIVE', which is the state of a transaction that is in progress, not a rollback state. If a rollback had occurred, the entry would have been deleted or would no longer be reported as active, so this result cannot support the rolled-back conclusion.

The transaction has been committed

Why wrong: A committed transaction also disappears from V$TRANSACTION once the commit is processed, and the STATUS column for a surviving row would not remain 'ACTIVE' after commit. Since the returned row shows STATUS='ACTIVE', the transaction is still holding its Undo segment resources and has not reached the commit point; therefore, interpreting this output as committed is incorrect.

No transactions are currently active

Why wrong: The query returns one row, and in Oracle's V$TRANSACTION view each row corresponds to one currently active transaction. A result of zero rows would be required to conclude that no transactions are active; the presence of a single row with STATUS='ACTIVE' directly refutes that claim and demonstrates that at least one transaction is in progress.

Analyze the general log file using grep and tail to search for SELECT INTO OUTFILE

Why wrong: The general log records every connection and submitted statement only while it is actively being written; it is frequently rotated or truncated, and in this scenario it has already discarded everything older than one hour. Searching a file that only contains the most recent hour with grep or tail cannot reveal a SELECT INTO OUTFILE that occurred three hours ago. Additionally, the general log is disabled by default, so its mere presence for the last hour does not imply it was capturing events at the time of the incident.

Examine the InnoDB redo log files to find the SELECT INTO OUTFILE command

Why wrong: InnoDB redo logs are a physical, cyclic write-ahead log that only captures changes to data pages, not the logical SQL text that caused those changes. A SELECT INTO OUTFILE does not modify any InnoDB page, so it leaves no redo record whatsoever, and even if it did, redo logs are overwritten in a loop so records from three hours ago are likely already gone. Examining redo logs for a SELECT command is therefore both conceptually impossible and forensically useless for this incident.

Enable MySQL audit logging and wait for the activity to reoccur

Why wrong: Enabling MySQL audit logging now can only capture future statements, and the suspected exfiltration already occurred three hours ago; no retrospective data will be collected by turning on the plugin after the fact. The audit log plugin, particularly MySQL Enterprise Audit, must be loaded and configured before events occur, and it does not scan or read previous statements from binary logs or any other source. This action would be appropriate for ongoing monitoring, but it cannot recover the historical SELECT INTO OUTFILE evidence needed for the investigation.

mysqldump --binlog binlog.000012

Why wrong: mysqldump is a logical backup client that extracts schema and table data as SQL statements by querying the live database server, not by reading binary log files. It has no --binlog option for parsing binlog.000012, and even if an option with a similar name were present, its purpose is to produce a consistent database dump, not to decode statement-based or row-based binlog events. Therefore, it cannot serve as a forensic binary log reader.

mysqlimport --binlog binlog.000012

Why wrong: mysqlimport is a command-line utility designed solely to load delimited text files into tables using LOAD DATA INFILE syntax over the MySQL protocol. It expects an input file such as data.tsv containing column data, not a binary log; the --binlog argument is not a recognized option, and the tool cannot interpret the event headers, checksums, or base64 row images contained in binlog.000012. Running it against a binary log would fail immediately, and it has no capacity for forensic event parsing.

mysqlcheck --binlog binlog.000012

Why wrong: mysqlcheck is a table maintenance client used to CHECK, REPAIR, ANALYZE, and OPTIMIZE tables on a live MySQL server. It operates exclusively on database and table name arguments, performing integrity checks or repairs in place, and it lacks any code path for reading binary log files or extracting SQL statements from them. The --binlog option does not exist in mysqlcheck, and binlog.000012 is neither a database nor a table identifier, so the command is invalid both syntactically and semantically.

sys.dm_exec_sessions

Why wrong: sys.dm_exec_sessions is a dynamic management view that exposes one row per currently authenticated session on the SQL Server instance. While it does include a login_name column, it is a point-in-time snapshot of active sessions, not an audit log of failed authentication attempts; a failed login never creates a session, so this DMV contains no record of rejections such as error 18456.

sys.dm_tran_locks

Why wrong: sys.dm_tran_locks is a DMV that tracks lock resources currently held or awaited by active transactions, such as row, page, or table locks used for concurrency control. It has no relationship to authentication or login events, and because failed logins do not start a transaction or request a lock, querying this view yields no information about unsuccessful connection attempts.

sys.dm_exec_requests

Why wrong: sys.dm_exec_requests returns information about requests currently executing within the SQL Server instance, including session ID, SQL text, and wait type, but it only reflects active work at the moment of query execution. A completed or failed login attempt does not constitute an active request, and the DMV does not maintain historical login data, so it cannot reveal prior authentication failures.

Connect device to UFED workstation, then Enable USB debugging, then Select extraction type, then Execute extraction and parse data

Why wrong: Connecting the device to the UFED workstation before enabling USB debugging is flawed because the device will default to a charge-only or MTP mode without an active ADB daemon, so the workstation cannot issue the privileged commands needed for acquisition. USB debugging authorization and the RSA key handshake must occur immediately after physical connection; if the mode is already fixed in a non-debug state, the workstation may enumerate it as a generic mass-storage device and fail to load the correct extraction profile. Reordering the steps means the connection step does not establish a valid forensic communication channel, making the later extraction step invalid.

Enable USB debugging, then Select extraction type, then Connect device to UFED workstation, then Execute extraction and parse data

Why wrong: Enabling USB debugging first is correct, but choosing the extraction type before connecting the device is premature because the available extraction methods (logical, file system, or physical) depend on the device's chipset, Android version, and bootloader state, which can only be identified after the device is enumerated on the UFED workstation. Without a live connection, the workstation's software cannot match the device model to a compatible extraction profile, so the operator may select a method that is unsupported on the connected handset. The sequence must allow the workstation to poll the device parameters first, then present only the valid extraction options.

Select extraction type, then Connect device to UFED workstation, then Enable USB debugging, then Execute extraction and parse data

Why wrong: Selecting the extraction type at the very beginning inverts the entire acquisition workflow because no device parameters—Android version, build number, root status, or connected state—have been established to inform that choice. The UFED software cannot validate a pre-chosen extraction method against a device it has not yet seen, and without USB debugging enabled there is no ADB channel to receive the commands once the extraction begins. This ordering is fundamentally premature: it assumes device information that can only be gathered through a debugging-enabled connection, so the subsequent steps cannot correct the initial misconfiguration.

Signature carving: Reassembles files from non-contiguous disk blocks.

Why wrong: Stating that signature carving reassembles non-contiguous disk blocks confuses it with fragmentation carving. Signature carving fundamentally operates on contiguous regions defined by a start and end marker; it does not analyze block relationships or file system metadata to reorder scattered sectors. That black-box reconstruction of fragmented data is precisely the distinct domain of fragmentation carving, which relies on additional evidence beyond header and footer signatures.

Fragmentation carving: Applies statistical analysis to determine file boundaries.

Why wrong: Attributing statistical analysis to fragmentation carving mischaracterizes its methodology. Fragmentation carving is rooted in structural and relational clues—examining file system allocation maps, FAT chains, and block consecutiveness—to determine the correct order of recovered fragments. Statistical carving, by contrast, works independently of file system metadata by measuring data characteristics like entropy or frequency distributions to identify file boundaries, a fundamentally different approach.

Statistical carving: Recovers files by identifying unique header and footer signatures.

Why wrong: Claiming that statistical carving recovers files via unique header and footer signatures collapses it into signature carving. Statistical carving does not look for literal magic numbers; instead, it computes statistical properties of the data (entropy, character frequency, format-specific model probabilities) to estimate where a file starts and ends. While signature carving is deterministic pattern matching, statistical carving is probabilistic boundary inference, making it suitable for formats without rigid or consistent signatures.

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

Why wrong: Applying a transaction log backup taken after the DROP TABLE statement re-executes that same DDL transaction during roll-forward, which deletes the table again. The log backup contains the drop as a committed operation, so restoring it moves the database forward through the drop rather than around it. A log restore alone also cannot run until a full database backup has been restored first.

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

Why wrong: NO_TRUNCATE is a BACKUP LOG option, not a RESTORE option; it is used to create a tail-log backup when the data files are damaged or offline, preventing truncation of the log. Issuing RESTORE LOG ... WITH NO_TRUNCATE is invalid for restoring a dropped table because it neither applies backup files nor supports a stop-point. Recovery of the dropped table requires STOPAT/STOPBEFOREMARK point-in-time restore, not this flag.

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

Why wrong: Restoring only the most recent full backup and skipping the transaction log backups puts the database at the full backup's point-in-time, so every modification made after that backup is permanently lost. Although the table will still exist if the full backup predates the DROP, all later inserts, updates, and deletes are absent, and any log-backed transactions are not reapplied. To get back to the exact pre-drop database state, you must restore the log chain, not just the full backup.

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

Why wrong: Restoring the full backup from midnight and the 1:45 PM log backups to a separate server would only reconstruct the database as of 1:45 PM, completely ignoring the active transaction log segment containing all transactions from 1:45 PM to the incident. This un-backed-up tail may hold the attacker's activity and is essential for both forensic analysis and complete recovery. You must first perform a tail-log backup; otherwise, the current state of evidence is lost. Restoring to a separate server is otherwise a sound analysis technique, but it is invalid without the tail log.

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

Why wrong: Shutting down the SQL Server service does not safely capture the transaction log tail; in fact, on restart the database will run crash recovery, rolling forward and rolling back transactions, which modifies the log and can obscure the exact forensic timeline. Additionally, restoring the database from backup would overwrite the existing database files and destroy the very evidence you need to examine. The correct approach is to back up the tail-log first, and only then consider whether a shutdown is necessary, but never as a substitute for a proper log backup.

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.

Why wrong: This plan incorrectly treats the 2:15 PM backup as a source of recovery, but that backup was taken after the attack and therefore contains the malicious transactions that you need to exclude. A point-in-time recovery should stop at the last known good point before the attack, not after it, and you must incorporate the tail-log backup to capture the current log. Applying the 2:15 PM backup would reintroduce the intrusion into any restored environment and is the opposite of a forensic recovery.

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

Why wrong: If the MySQL OS account lacked write permission to the data directory, startup would fail earlier with explicit 'Permission denied' or 'Can't create/write to file' errors when opening or creating files. Inode and file-access checks happen before InnoDB reads the system tablespace header. An InnoDB corruption error like 'Cannot open datafile' or 'Database page corruption' is not a permission complaint, so this hypothesis does not match the exhibit.

The binary log is full and cannot be rotated.

Why wrong: The binary log records committed transactions and is written after the storage engine accepts them; it is never read during InnoDB initialization or crash recovery. A full binary log or a log-rotation failure would generate runtime errors such as 'Could not write to binary log' or 'disk full' at commit time, not an InnoDB tablespace header failure. InnoDB recovery relies on redo logs (ib_logfile*), not binlogs, so a binlog problem cannot prevent InnoDB from loading ibdata1.

The server ran out of memory due to high innodb_buffer_pool_size.

Why wrong: An oversized innodb_buffer_pool_size might trigger the OS out-of-memory killer or an 'InnoDB: Cannot allocate memory' error before any tablespace file is opened. Memory allocation failures typically appear as 'Out of memory' or a process kill, not as a datafile corruption or checksum mismatch. The exhibit's error indicates a persistent I/O or integrity problem in ibdata1, not a transient memory shortage, so this explanation is inconsistent with the observed message.