iQueryContacts

Parsing Apple Contacts, data from the AddressBook.sqlitedb database.

While largely overlooked, the AddressBook.sqlitedb database offers unique value joining and requires an advanced SQL Query solution. Contact setup can reveal the relationship or significance of a contact to the device user, beyond just the name, phone number, or username. Additional data entered can provide insight into the user’s relationship, length of interaction, or imported data from third-party applications.

This SQL query goes far beyond the standard, simple select-column query to bring you more:

• Prefix, First Name, Middle Name, Last Name, and Suffix info
• Phone / Email / Misc fields with their label prefix centralized to one column
  • With an added CTE to normalized label text inside _$!<Label>!$_ placeholders, so _$!<Mobile>!$_ displays as Mobile
• Properly ordered multi-line Address(es)
• Profession from Organization, Department, Title, Organization Phonetic
• Social Media Account(s) and Usernames, ordered alphabetically by the service provider
• Note, if added for the contact
• Birthday and, if present, Chinese lunar (AlternateBirthday) dates → real Gregorian year + full traditional description (with leap month & stem-branch)
  • Solving the problem of how Apple gets 1985乙丑年十月初六 out of 1985-11-17 when the database value is chinese,0,78,2,10,6
• Group membership
• Phonetic / Pronunciation Name
• Date Values
  • Contact Creation / Save / Modification timestamps

Query Results Preview:

Prefix	First Name	Middle Name	Last Name	Suffix	Phone / Email / Misc
Miss	Abby	A	Normal		Mobile: (555) 111-3636
					Home: (555) 123-3636
					Work: (555) 222-3636
					School: (555) 333-3636
					iPhone: (555) 444-3636
					Apple Watch: (555) 555-3636
					Main: (555) 666-3636
					HomeFAX: (555) 777-3636
					WorkFAX: (555) 888-3636
					Pager: (555) 999-3636
					Other: (555) 456-3636
					Home: abby@fake.com
					Work: anormal@scienceandtesting.co
					School: anormal@collegefancy.edu
					iCloud: anormal@notgmail.com
					Other: anormal@throwaway.org
					Anniversary: 2004-07-06
					Other: 2016-02-12
					HomePage: abnormalthoughts.com
					Home: blogspace.abby.com
					Work: scienceandtesting.co
					School: collegefancy.com
					Other: absolderthoughts.blog.com
					Mother: Mother Dearest
					Father: Father Dearest
					YoungestSister: Littlest Sue
					BrotherInLawSistersHusband: Tom
					Manager: Smart Guy

Query Results Continued:

Address(es)	Profession
Home: 123 Main Street, Bestburger, AL, 36801, United States, US	Organization: Science and Testing Co.
Work: Science and Testing Co 123 Beaker Ave, Bestburger, AL, 36801, United States, US	Department: Measuring (Clean Up)
Manager’s Remote Address: 233 12th Street, Columbus, GA, 31901, United States, US	Title: Measuralist
	Organization Phonetic: Sigh-uhns And Tes-ting Coh

Query Results Continued:

Account(s)	Note	Birthday
AIM: abbynormal36	Abby Normal is the best at measuring.	Birthday: 1985-11-17
Custom: abbynormal36		Alternate Birthday: 1985乙丑年十月初六
Facebook: Abby Normal
flickr: abbynormal536
GoogleTalk: abbynormal36
ICQ: abbynormal36
Jabber: abbynormal36
linkedin: Abby Normal
MSN: abbynormal36
myspace: abbynormalzone
Signal: abnormal36
sinaweibo: Abby Normal
Skype: abby_normal
twitter: abbynormal3636
Yahoo: abbynormal36

Query Results Continued:

Group	Phonetic / Pronunciation Name	Date Values
VIPs	Phonetic First Name: Ab-ee	Created: 2025-11-10 14:16:57
	Last Name Pronunciation: Nor-muhl	Saved:
		2025-11-10 14:16:57
		2025-11-10 14:17:06
		2025-11-10 14:17:24
		2025-11-10 14:17:38
		2025-11-10 14:17:42
		2025-11-10 14:23:55
		2025-11-10 14:24:02
		Modified: 2025-11-10 14:24:02

Deep Dive – What Each Column Does & How the Query Functions

1. Prefix, First Name, Middle Name, Last Name, Suffix

These values are pulled straight from the ABPerson table, if present for the contact.

2. Phone / Email / Misc

• The CleanLabels CTE strips Apple's _$!<Label>!$_ standard values and returns more user-friendly data such as Mobile, Work, etc.
• The CustomFields CTE detects phone numbers (many formats), big integers → converts Apple’s Cocoa epoch (978307200 = 2001-01-01) to real dates
• All other values are left untouched and the outer query group_concat(…, CHAR(10)) ensures one entry per line in the query result

3. Address(es)

• Reconstructs full multi-line addresses in the correct order (Street → City → State → ZIP → Country)
• Country code is upper-cased (us → US)
• If a label exists (Home/Work), it’s prepended: Home: 123 Main Street, Bestburger, AL, 36801, United States, US

4. Profession

Concatenation of Organization, Department, JobTitle and organization phonetic field, only if any of them exist.

5. Account(s)

Apple stores instant-message and social-media accounts in several different formats depending on the OS version and application.

This section unifies all variations into a consistent output.

Data Storage

Property Value (ABMultiValue.property)	Username	Service
13	ABMultiValue.value	ABMultiValueEntry.value
46	ABMultiValueEntry.value	ABMultiValueEntry.value

The ABMultiValueEntryKey table holds the key names for these entries. Although the table contains only a single value column, SQLite assigns each row an implicit surrogate key through its internal rowid.

ABMultiValueEntry.key uses this surrogate key as its reference.

Example:

	ABMultiValueEntryKey table	ABMultiValueEntry table
surrogate key	value	parent_id	key	value
7	username	61429	7	abbynormal36
8	service	61429	8	AIM

The CTEs MVE_Username, MVE_Service, MVE_ProfileEntries, MV_InstantMessage, and MV_Profile normalize the various storage patterns.

CTE UnifiedAccounts unifies the possible variations, our group_concat(formatted_account, CHAR(10)) ensures one entry per line, and ORDER BY service COLLATE NOCASE ASC sorts the Account(s) alphabetically by service name.

6. Birthday

Users can set birthdays for their contacts. This value is stored in ABPerson.Birthday and appears as a timestamp like -477230400.0. A simple datetime conversion turns this into a readable format: 1985-11-17.

If a birthday is entered, the user can also select "add birthday" again, which defaults to the Chinese lunar birthday (e.g., 1985乙丑年十月初六). This value is stored in ABPerson.AlternateBirthday, but its format is not immediately intuitive. Instead of storing the displayed lunar date directly, it appears as:

chinese,0,78,2,10,6

Or, in general terms:

chinese,<is_leap 0/1>,<era_version>,<year_index>,<lunar_month>,<lunar_day>

Here, era_version represents 60-year cycles:

• 76 → 1864–1923
• 77 → 1924–1983
• 78 → 1984–2043 (current)
• 79 → 2044–2103
• ... and so on.

The Gregorian year can be calculated with the formula:

1863 + 60×(era_version − 76) + year_index

Example:

chinese,0,78,2,10,6

• is_leap = 0 → normal lunar month
• era_version = 78 → current 60-year cycle (1984-2043)
• year_index = 2 → offset within the era
• Lunar month = 10
• Lunar day = 6

Gregorian year = 1863 + 60 x (78-76) + 2 = 1985

→ Chinese lunar date: 1985乙丑年十月初六 (corresponding to 17 Nov 1985 in Gregorian calendar)

Tools like Magnet AXIOM parse this raw string and convert it to a readable date, but historically it was displayed as-is because the encoding was undocumented.

Query Deconstruct

Explanation of the CTEs

The CTEs (data, split, final_split, etc.) are used to break down the raw AlternateBirthday string into its components and compute a usable Gregorian year.

1. data

   • Selects ROWID, First, Birthday (converted to a human-readable date), and AlternateBirthday.
   • Filters only rows where AlternateBirthday begins with 'chinese'.
   • Converts the Apple epoch (Birthday + 978307200) to standard YYYY-MM-DD format.

2. split

   • Begins the process of splitting the comma-separated AlternateBirthday string.
   • part2 contains everything after the first comma.

3. final_split

   • Extracts is_leap_str (whether the lunar month is leap or not) and the rest of the string (rest) for further parsing.

4. version_step

   • Extracts version (the era_version corresponding to 60-year cycles) from rest.

5. year_split

   • Converts is_leap_str to an integer.
   • Prepares rest2 for parsing year_index.

6. month_day_split

   • Extracts year_index (offset within the era) and rest3 (month and day indices).

7. final_calc

   • Computes real_year using the formula:

   real_year = 1863 + 60 * (version - 76) + year_index

   • Computes sb_index, which corresponds to the 60-year cycle stem-branch pair (甲子, 乙丑, etc.) used in the Chinese calendar.

In short: Each CTE incrementally parses the comma-separated string chinese,<is_leap>,<era_version>,<year_index>,<month>,<day> into separate columns (is_leap, version, year_index, month, day) and calculates a Gregorian year and a Chinese sexagenary cycle index.

Explanation of the final query portion

The NULLIF(TRIM(COALESCE(...))) AS "Birthday" part is responsible for constructing a readable birthday string, combining both the Gregorian and Chinese lunar birthdays.

1. Gregorian birthday:

CASE 
  WHEN ABPerson.Birthday IS NOT NULL AND ABPerson.Birthday <> ''
  THEN 'Birthday: ' || SUBSTR(DATETIME(ABPerson.Birthday + 978307200, 'UNIXEPOCH'),1,10)
END

• Converts the Apple timestamp to YYYY-MM-DD and prefixes with Birthday:.

2. Chinese lunar birthday:

CASE
  WHEN ABPerson.AlternateBirthday IS NOT NULL AND ABPerson.AlternateBirthday <> ''
  THEN CHAR(10) || 'Alternate Birthday: ' || real_year || ...
END

• Builds the string starting with Alternate Birthday: <year>
• Appends the stem-branch (sexagenary) year using sb_index (0–59 → 甲子, 乙丑, …)
• Adds '闰' if is_leap = 1
• Converts month and day indices to Chinese lunar month and day names (正, 二, … / 初一, 初二, …)

3. NULLIF(TRIM(COALESCE(...)))

• Ensures that if both Gregorian and lunar birthdays are missing or empty, the field becomes NULL rather than a blank string.

In effect: This query outputs a single formatted string like:

Birthday: 1985-11-17
Alternate Birthday: 1985乙丑年十月初六

7. Group

• All groups the contact belongs to, comma-separated.
• Note: When a contact is added to a group, the database creates an additional ABPerson row for that contact. The ABPerson.ROWID is joined to ABGroupMembers.member_id, and the corresponding group name is stored in ABGroup.Name.

8. Phonetic / Pronunciation Name

Stores all six phonetic/pronunciation fields as entered by the user:

• FirstPhonetic, FirstPronunciation
• MiddlePhonetic, MiddlePronunciation
• LastPhonetic, LastPronunciation

For Example:

Phonetic First Name: Ab-ee
Last Name Pronunciation: Nor-muhl

9. Date Values

• CreationDate – The timestamp when the contact was first created.
• ModificationDate – The timestamp of the last modification to the contact.
• save_timestamp – Every save event recorded in the joined ABPersonChanges + ClientSequence tables.

Created: 2025-11-10 14:16:57
Saved:
   2025-11-10 14:16:57
   2025-11-10 14:17:06
   2025-11-10 14:17:24
   2025-11-10 14:17:38
   2025-11-10 14:17:42
   2025-11-10 14:23:55
   2025-11-10 14:24:02
Modified: 2025-11-10 14:24:02

Examiners are typically familiar with CreationDate and ModificationDate. However, save_timestamp values provide a more granular view of changes over time and can be invaluable in forensic analysis to track contact edits.

Note: When evaluating timestamps, it is important to distinguish between changes initiated by the user and those generated automatically by the system. Accurate forensic interpretation requires correlating timestamps with other evidence, such as application logs, system events, or network sync records, to reliably determine the source and intent of each change.

Where to Use:

• The SQL queries will work in most SQLite database viewers able to execute SQL queries.
• The AddressBook_Contacts.xml file is a Magnet AXIOM Custom Artifact, which can be added to Magnet AXIOM Process through Tools > Manage custom artifacts > ADD NEW CUSTOM ARTIFACT.

File Location

This query operates on AddressBook.sqlitedb, the database storing Contacts information on Apple devices. It is found in all iPhone extractions and iTunes backups.

Disclaimer

All names, phone numbers, email addresses, URLs, addresses, and any other personal or identifying details appearing in this document are entirely fictitious and used solely for instructional and illustrative purposes. Any resemblance to real persons—living or deceased—or to actual organizations, accounts, or contact information is purely coincidental and unintentional. If you believe any example data in this document corresponds to real personal information and would like it removed, please contact me at james@metadataforensics.com.