SQL Query to extract contacts from iCloud Backup - AddressBook.sqlitedb

iCloud-AddressBook-Recovery.sql

--
-- How to recover iPhone contacts from Backup files:
--
-- Recover contacts from AddressBook.sqlitedb of iCloud and iTunes backup
-- I used https://github.com/horrorho/InflatableDonkey in order to download AddressBook.sqlitedb from iCloud Backup the last backups made, 
-- in order to recover some of my Wife's contacts that got lost while reconnecting her iPhone to her work's Exchange server.
-- * If you have 2FA for your Apple Account, you won't be able to login to the iCloud using the tool above, 
--   without supplying the 6 digit token. 
--   Use the following method in order to login from cURL, and by that get the Two step token on your Apple Device
--   (Copy the next 4 lines to bash shell, modify the APPLE_ID and APPLE_PASS env vars to your own details):
--
--   APPLE_ID="email" && APPLE_PASS="password" && curl -XPOST "https://p12-setup.icloud.com/setup/ws/1/login?clientBuildNumber=1P24&clientId=$(uuidgen)" \
--   -d '{"apple_id":"'"$APPLE_ID"'","password":"'"$APPLE_PASS"'","extended_login":false}' \
--   -H "Origin: https://www.icloud.com" -H "Referer: https://www.icloud.com" \
--   -H "User-Agent: Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/35.0.1916.114 Safari/537.36"
--
-- Source for original SQL query: https://gist.github.com/laacz/1180765
-- I have modified it a bit, added more fields that may have relevant data.
-- Open the file with sqlite3 or some sqlite3 utility. I used DB Browser for SQLite (on MacOS)

select ABPerson.ROWID
     , ABPerson.first
     , ABPerson.last
     , ABPerson.Organization as organization
     , ABPerson.Department as department
     , ABPerson.Birthday as birthday
     , ABPerson.JobTitle as jobtitle

     , (select value from ABMultiValue where property = 3 and record_id = ABPerson.ROWID and label = (select ROWID from ABMultiValueLabel where value = '_$!<Work>!$_')) as phone_work
     , (select value from ABMultiValue where property = 3 and record_id = ABPerson.ROWID and label = (select ROWID from ABMultiValueLabel where value = '_$!<Mobile>!$_')) as phone_mobile
     , (select value from ABMultiValue where property = 3 and record_id = ABPerson.ROWID and label = (select ROWID from ABMultiValueLabel where value = '_$!<Home>!$_')) as phone_home
     , (select value from ABMultiValue where property = 3 and record_id = ABPerson.ROWID and label = (select ROWID from ABMultiValueLabel where value = '_$!<Main>!$_')) as main
     , (select value from ABMultiValue where property = 3 and record_id = ABPerson.ROWID and label = (select ROWID from ABMultiValueLabel where value = '_$!<Other>!$_')) as other
     , (select value from ABMultiValue where property = 3 and record_id = ABPerson.ROWID and label = (select ROWID from ABMultiValueLabel where value = 'נייד')) as mobile
     , (select value from ABMultiValue where property = 3 and record_id = ABPerson.ROWID and label = (select ROWID from ABMultiValueLabel where value = '_$!<HomeFAX>!$_')) as fax_home
     , (select value from ABMultiValue where property = 3 and record_id = ABPerson.ROWID and label = (select ROWID from ABMultiValueLabel where value = 'VOICE')) as voice

     , (select value from ABMultiValue where property = 4 and record_id = ABPerson.ROWID and label is null) as email
     
     , (select value from ABMultiValueEntry where parent_id in (select ROWID from ABMultiValue where record_id = ABPerson.ROWID) and key = (select ROWID from ABMultiValueEntryKey where lower(value) = 'street')) as address
     , (select value from ABMultiValueEntry where parent_id in (select ROWID from ABMultiValue where record_id = ABPerson.ROWID) and key = (select ROWID from ABMultiValueEntryKey where lower(value) = 'city')) as city
  from ABPerson
order by ABPerson.ROWID
;