QuickBooks Internals | Security | SQL and a Major Design Flaw

As highlighted in the previous parts QuickBooks company files are SQL Anywhere database files and other QB formats are based on SQL Anywhere features as well. It should be obvious that SQL code (Watcom SQL - the dialect of SQL supported by SQL Anywhere) is important part of QB workflow, so in this part we will take a look at some important procedures, events and triggers and major security implications of this design.

Let's look at basic startup steps. As soon as user logs into database ISYSOPTION (SYSOPTION) table is checked for "login_procedure" parameter assigned to this user. Defaults values are: CreateDBBackup for "backup_user", GetFileMetaData for "metadata_user" and LoginProc for normal QB users.

SQL procedures are stored in ISYSPROCEDURE (SYSPROCEDURE) table.
CreateDBBackup and GetFileMetaData are designed to perform necessary operations and drop connection with INVALID_LOGON error.
For example, GetFileMetaData is used to create a file with database and ABMC_TSAFE table details to access database backdoor without proper credentials to login. LoginProc is quite a long one, but basic steps are to parse connection parameters, create ND and DSN files, verify number of users against version and license allowed, etc.
Event's handlers in ISYSEVENT (SYSEVENT) are also worth mentioning and can be interesting subject for analysis.

Why is it important?
SQL code used in QuickBooks is arguably more powerful than VBA in MS Access or MS Excel and at the same time it is completely hidden and starts with every opened file. Functions like xp_cmdshell, xp_write_file, xp_sendmail are included by default as part of more than 300 built-in functions and external libraries are easy to call on Windows and Mac. Rootkit installation in just 3 lines of code: get data from table -> xp_write_file -> xp_cmdshell. Moreover real database content is hidden from QuickBooks users, so there is virtually unlimited storage for code, stolen data, etc.

Can it be changed / edited / replaced?
Yes. QBW and QBA company files are a bit tricky to modify directly, but with SQL Anywhere knowledge it is a very easy task. QBX (accountant's transfer copies) and QBM (portable company files) are even easier to modify. QBX and QBM are compressed SQL dumps, so SQL modification is as hard as replacing zlib compressed "reload.sql" inside compound file. You can use QuickBooks Forensics to extract SQL script from QBX or QBM and take a look at several megabytes of SQL code used.

Is there SQL code validation / integrity check?
No. Moreover SQL Anywhere is built for embedded applications so there are number of tricks and functions (like SET HIDDEN clause) to protect SQL code from analysis. Current workflow forces QuickBooks to login and execute large portion of SQL code before any data can be accessed from database and at that point modified database can send back any fake data.

Is there a proof of concept?
Yes. Below you can find company file created in QB 2009 and modified to start Notepad.exe upon every user login (Admin, no pass). This example will work in any version including 2016 (US, CA, UK) - login procedure execution is required in order to check QB version or edition or to start update, so you will see Notepad before QB "wrong version" error message.
Proof of concept: QBFp.zip

UPDATE: QB critical updates (May 09 / May 20) for version 2016 switched on SQL Anywhere version 16 -sbx+ option (sandbox) and disabled xp_cmdshell and external library calls. Prior version are still partially vulnerable unfortunately (executable files can be extracted to arbitrary location or program / system .dll replaced, etc.). It is still important to select QB files location for version 2016: current folder and all sub-folders are accessible in sandbox mode, thus it is important to avoid disk roots, desktop, folders with important files, folders with any executable files, etc.

Are there any internal risks?
It should be obvious that all outside files (qbw, qba, qbx, qbm) are dangerous but this design can be exploited internally as well.
As noted in the Part 1 all normal database users are equal in database access rights (QB controls data access), thus any user with login and password and some SQL Anywhere and QB knowledge can change login procedure to cache Admin credentials for example.