Conociendo a los parientes: Bases de datos SQLite

Me puse en contacto con un colega que necesitaba un poco de ayuda el análisis de una base de datos SQLite. Fue la base de datos myspace.messaging.database # ubicada en el » Users AppData Local google chrome userdata data por defecto plugin engranajes google messaging.myspace.com http_80 «carpeta. Yo no, y todavía no sé mucho acerca de este archivo, pero parece contener mensajes de correo electrónico myspace.

los desafíos de SQLite

Seamos realistas: SQLite está en todas partes. La comprensión es esencial para los buenos exámenes, y una gran parte de esa comprensión viene de aprender las instrucciones SQL. Hay muchas fuentes en línea buenos para aprender SQL, y uno de mis favoritos es w3schools.com

Pero, para los médicos forenses digitales, hay otro desafío más allá de comandos SQL comprensión -. Entender la construcción y relaciones de las tablas. SQLite es un relacional de base de datos, y las mesas están destinados a ser relacionados entre sí para producir un resultado no es posible o práctico de una sola tabla. Saber cómo se pretendía la tabla a utilizar puede ser muy difícil … después de todo, una base de datos SQLite es más parecido a un gabinete de archivos, no una secretaria que utiliza el archivo de gabinete.

Por ejemplo, el secretario puede colocar registros bancarios de la compañía en un archivo llamado «registros financieros» o ella puede ponerlos en un archivo llamado «Alcachofas». Realmente no importa, porque ella sabe lo que pasa en el archivo. Alguien que viene detrás de ella no tendrá mucho problema para encontrar los registros de los bancos en el archivo de registros financieros, pero podría pasar por alto por completo en el archivo de alcachofas. El punto es, sin el secretario, que podría ser muy difícil de entender el sistema de archivo.

Bases de datos SQLite pueden ser muy parecido a eso. Se puede ver la estructura o el esquema como se le llama, con mucha facilidad. Pero lo que no se entiende tan fácilmente es cómo se destina el dispositivo a utilizar. Ese misterio es por lo general encerrado en la aplicación que utiliza la base de datos, pero no se explica en la propia base de datos.

Conseguir una pista Para estar seguros, no puede ser pistas sobre cómo las tablas de una base de datos se relacionan entre sí. Los nombres de tabla y de campo hablan volúmenes. Una base de datos llamada «AddressBook.db» con dos tablas llamadas «Nombres» y «Direcciones» que tienen un campo en común que se llama «SubjectID» no es demasiado difícil de entender. Si tenemos la suerte de poder ejecutar la aplicación que utiliza la base de datos y probar nuestras inferencias sobre la base de la salida de solicitudes, nuestra confianza crece (y nuestra comprensión, si es compatible con el resultado, podría ahora ser considerado fiable).

Mis pistas favoritas por el momento son sentencias SQL ‘Ver’. Estas son tablas virtuales que dibujan sus datos de otras tablas de la base de datos (o una base de datos adjunta). Mediante el estudio de una declaración vista, se obtiene una visión del creador de base de datos de cómo se pretendía la base de datos que se utilizará … al menos en uno de capacidad. Piense en una vista como una macro: se ahorra al usuario la molestia de la base de datos repetidos escribir una consulta de uso frecuente. Y, si la consulta se utiliza con frecuencia, entonces usted tiene una buena idea de cómo se pretendía la base de datos que se utilizará.

¿Qué si no hay pistas?
¿Qué pasa con las circunstancias en las que no hay pistas en la base de datos para ayudar a comprender su uso. Bueno, si hay realidad no hay pistas, entonces la única respuesta segura es nos fijamos en los datos plana, es decir, nos fijamos en las tablas de forma individual y no nos relacionamos ellos de ninguna manera. Pero, a menudo hay pistas menos obvias que pueden revelar una relación subyacente … lo que me lleva al punto de este artículo.

Filas latentes

examinadores de huellas latentes conocen el término «latente» significa oculto o invisible. Huellas dactilares latentes deben ser reveladas a ser visto por algún método externo, como polvo de huellas dactilares. Tablas de SQLite tienen un campo latente, por así decirlo. Y, podemos revelarla para ayudar a formar relaciones en una base de datos SQLite />.
Considere la base de datos # myspace.messaging.database he mencionado en el párrafo abierta. Cuenta con el siguiente esquema:

CREAR MESA VIRTUAL AuthorData USO fts2 ( AuthorDisplayName, authorUsername);
CREATE TABLE AuthorData_content (c0AuthorDisplayName , c1AuthorUserName);
CREAR TABLA AuthorData_segdir ( entero nivel, idx entero, número entero start_block, entero leaves_end_block, entero end_block, gota raíz, clave primaria (nivel, idx));
CREAR AuthorData_segments DE MESA (bloque blob);
CREATE TABLE AuthorMetaData (AuthorId INTEGER PRIMARY KEY, AuthorImageUrl TEXT);
CREATE TABLE MessageData virtual usando fts2 (asunto, cuerpo);
CREAR TABLA MessageData_content (c0Subject, c1Body);
CREATE TABLE MessageData_segdir (entero nivel, idx entero, número entero start_block, leaves_end_block entero, entero end_block, gota raíz, clave primaria (nivel, idx));
CREAR MessageData_segments DE MESA (bloque blob);
CREAR MessageMetaData DE MESA (IdMensaje INTEGER PRIMARY KEY, RecipientId INTEGER, INTEGER AuthorId, Carpeta INTEGER, INTEGER Estado, CreatedDate INTEGER);
CREAR UserSettings DE MESA (UserId INTEGER PRIMARY KEY, MachineID texto, entero Habilitado, marca_de_tiempo, LastSyncTimeStamp INTEGER, FirstRunIndexPass INTEGER, INTEGER FirstRunIndexTargetCount, OldestMessageId INTEGER, INTEGER LastServerTotalCount);
CREATE INDEX AuthorIdIndex EN MessageMetaData (AuthorId, RecipientId);
CREATE INDEX StatusIndex EN MessageMetaData (Estado, CreatedDate);

Ahora mira más de cerca en dos mesas de interés, MessageMetaData y MessageData_content:

CREAR MessageMetaData DE MESA (IdMensaje INTEGER PRIMARY KEY, RecipientId INTEGER, INTEGER AuthorId, Carpeta INTEGER, INTEGER Estado, CreatedDate INTEGER);

CREAR MessageData_content MESA (c0Subject, c1Body)

Al parecer, según los nombres de las tablas que MessageMetaData contiene información acerca de los mensajes, y MessageData_content contiene los mismos mensajes. Pero, ellos no comparten ningún campo que permiten las dos tablas que se relacionan. En otras palabras, que las filas de la tabla de metadatos corresponden a la fila de la tabla de contenido? ? Acaso ellos se corresponden en absoluto

Echemos un vistazo a nuestra primera pista o correspondencia:

$ sqlite3 myspace.messaging.database # database.db

$ sqlite3 myspace.messaging.database # database.db
1358

Ambos cuadros tener el mismo número de registros. Hmm, una pista? Es muy probable, sobre todo al estudio del contenido de la tabla y las tablas de contenido restante. De hecho la realización de un estudio similar, nos encontramos con otro grupo de la tabla de correspondencia:. AuthorMetaData y AuthorData_content también tienen el mismo número de registros (172, para ser exactos), pero no hay, campos interrelacionados obvias

A menos usted ha estudiado la construcción de SQLite en cualquier profundidad, es probable que no sepa que crea un campo de ‘rowid’ para cada mesa para que actúe como una clave principal. Si se crea una tabla con una clave principal definida, que la clave principal es sólo un alias a la rowid incorporado (con una excepción fuera del alcance de esta discusión). Pero el rowid no está representada en la tabla o esquema de la base, que es probablemente por eso que no sabía nada de él (al menos, no lo hice hasta que me compré un libro SQLite).

Conocer la existencia de la rowid, ahora puedo comprobar para ver si las dos tablas se emparejan campos ROWID:

$ sqlite3 myspace.messaging.database # database.db ‘select count (*) del MessageMetaData m, MessageData_content c donde m.rowid = c.rowid’
1358

No tenemos que confiar en el función de contar, echar un vistazo por ti mismo:

$ sqlite3 myspace . messaging.database # database.db ‘select m.rowid, c.rowid de MessageMetaData m, MessageData_content c donde m.rowid = c.rowid’
…
81407357 | 81407357
81416917 | 81416917
81504605 | 81504605
81505714 | 81505714
81530947 | 81530947
81569294 | 81569294

Bueno, ahora esto es aún más interesante. No sólo tenemos dos tablas con el mismo número de filas, pero tenemos dos tablas con campos en relación, es decir, rowid!

Entender que rowid es simplemente un autoincremento, único, entero de 64 bits menos que se declare expresamente lo contrario por medio de comandos de inserción y actualización. Pero, ¿es sólo una coincidencia? Consideremos: tenemos ROWIDs no secuenciales a lo largo de las dos tablas. Eso podría explicar por filas bajó de las tablas. Sin embargo, dos mesas, cada una con 1358 filas y cada fila tiene un rowid coincidente en la otra tabla? Eso es más que una coincidencia – es programática. La aplicación poblar las tablas es la asignación de los ROWIDs.

La prueba está en el pudín

Mi afirmación es que el myspace.messaging.database # database.db es la asignación de los ROWIDs ya que rellena las tablas relacionadas y enlaza las filas, haciendo coincidir rowid. Permítanme demostrar cómo se pueden asignar rowid:

sqlite> crear números de la tabla (entero dígitos);
sqlite> insertar en números de valores (dígitos) (1);
sqlite> insertar en números (dígitos) valores (2);
sqlite> Insertar en números valores (dígitos) (3);
sqlite> seleccione rowid, dígitos de los números;
1 | 1
2 | 2
3 | 3
4 | 3
sqlite> ; insertar en números (dígitos), ROWID valores (1000, 4);
sqlite> seleccione rowid, dígitos de los números;
1 | 1
2 | 2
3 | 3
4 | 3
1000 | 4

creado en tabla llamada» números «con un campo llamado» dígito «. Luego inserté tres filas en la tabla de los valores 1, 2 y 3 respectivamente. Si usted ha estado siguiendo a lo largo, que ahora sabemos que cada mesa SQLite también tiene un campo rowid, incluso si no se ha creado expresamente en la tabla por el usuario. El primero seleccione statemnt muestra el rowid autogenerado y junto con los dígitos que inserta.

La instrucción de inserción final es diferente. Aquí le asigno el rowid, en lugar de dejar que se rellena automáticamente por el motor de SQLite. Y, como una sede en la instrucción de selección final, tengo éxito en el establecimiento de un rowid no secuencial.

Uniendo todas las piezas

he demostrado de forma «oculta» que las tablas de bases de datos SQLite pueden estar relacionados. Se necesita un poco de conocimiento en la estructura de SQLite y el lenguaje de consulta SQL para dar a conocer estos datos, sin embargo. Si usted está en el hábito de confiar en los navegadores SQLite y mirando a tablas sin relacionarlos, entonces usted está realmente perdiendo una gran cantidad de datos.

Una vez más, permítanme ilustrar el uso de la base de datos myspace.messaging.database #. Echemos un vistazo a una fila en cada una de las mesas que he mencionado anteriormente:

El único indicio de relación, además de los nombres de tablas, es el AuthorID campo en MessageMetaData y AuthorMetaData. Pero todavía no hay una forma obvia para atar los metadatos para el contenido que más nos interese Su navegador GUI favorito tal vez hacer la pantalla más bonita, pero es igual de impotente.

Pero, ahora que usted tiene conocimiento de la rowid, y tienen un enlace a un tutorial sobre declaraciones SQLite, no estás muy lejos de ser capaz de hacer esto:

sqlite3-header myspace.messaging.database # database.db ‘select idmensaje, fecha y hora (CreatedDate / 1000, «unixepoch», «localtime») como Date, mm.AuthorID, c0AuthorDisplayName como «Autor Display Name», c1AuthorUserName como «Autor Nombre de usuario», c0subject como Asunto, c1Body como Cuerpo de MessageMetaData mm, MessageData_content mc, AuthorData_Content ac, AuthorMetaData am donde mm.AuthorID = am.AuthorID y am.rowid = ac.rowid y mm.rowid = mc.rowid límite 2; ‘
IdMensaje | Fecha | AuthorId | Autor Display Name | Autor Usuario | Asunto | Body1289081 | 2010-08-03 14:27:00 | 41265701 | Un usuario | auser | Hola |?? Hey, ¿qué pasa

Me pregunto, en el que la producción sería más bien examinar e informar

Adición

La última consulta no es realmente tan temible. Es justo el tiempo porque estamos agarrando siete campos de cuatro mesas, y la conversión de un sello de fecha. Pero, en realidad, es muy sencillo

Vamos a echar un vistazo:

seleccione
idmensaje,
datetime (createddate/1000 «unixepoch», «localtime») como Date,
<= span style "color: blue; font-family: Courier New, Courier, espacio sencillo, fuente -size: x-small; "> mm.AuthorID,
c0AuthorDisplayName como» Autor Display Name «,
c1AuthorUserName como» Autor Nombre de usuario «,
c0subject como Asunto,
c1Body como Body
de
MessageMetaData mm,
MessageData_content mc,
AuthorMetaData am,
AuthorData_Content ac
donde
mm.AuthorID = am.AuthorID
y am.rowid = ac.rowid
y mm.rowid = mc.rowid;

El seleccione cláusula simplemente recoge los campos que queremos mostrar. La función datetime convierte la hora unixepoch, que se registra en milisegundos, a la hora local. El «como» declaraciones han puesto el nombre de las columnas algo más fácil de usar y no se requieren.

El de Declaración simplemente declara qué tablas de consulta para los campos que estamos tratando de mostrar. Cada tabla va seguido de un alias que elegí hacer una referencia más fácil de nombres de campos comunes a más de una tabla. Por ejemplo, AuthorID se encuentra tanto en las mesas MessageMetaData y AuthorMetaData. Al dar MessageMetaData el alias de mm, ahora puedo hacer referencia al campo MessageMetaData.AuthorID como mm.AuthorID.

El donde declaración es un filtro. Mantiene las tablas ‘alineado’, por así decirlo. Se asegura de que sólo el autor de contenido correcto y el contenido del mensaje se devuelve para cada fila. Este blog es mucho tiempo en el diente, por lo que no voy a entrar en detalles que describe cómo funciona. Pero, de manera muy sucinta, el registro MessageMetaData se hace coincidir con un registro AuthorMetaData por AuthorID. El expediente AuthorMetaData coincide con su récord AuthorData_Content correspondiente por rowid. Por último, el registro MessageMetaData se adapta a su MessageData_content correspondiente, también por rowid.