Frank Sheeran (publicfranksheeran@gmail.com)
-
Lets C++20 and newer programs on Linux and Windows read and write to MySQL and MariaDB with prepared statements
-
Write FAR Less Code: other wrappers or the straight API may require 15-70x more code
-
Safety Features: checks many error sources and logs them in the highest detail possible; forbids several potentially unsafe operations
-
Lower Total Cost of Ownership: your code is faster to write; faster to read, understand, support and maintain; better time to market; higher reliability; less downtime
-
Comparable Performance: uses about the same CPU-seconds and wall-clock time as the raw interface, or two leading wrappers
-
Try it Piecemeal: just use it for your next SQL insert, select, update, delete, etc. in existing software. You should not need to rewrite your whole app or ecosystem just to try it.
-
Implemented As: 1 header of ~1500 lines
-
Use in Commercial Products for Free: distributed with the MIT License*
-
Support Available: the author is available for consulting, and there is a Facebook user's group
* See the accompanying file LICENSE.txt for details.
-
Large reduction in code: what takes 12 lines in Squalid may take 180-900 lines with other APIs.
-
App programmers need not check error state after every call: just one check at the end of a "prepare-bind-execute-loop over results-check errors" chain will suffice.
-
Error information is logged in detail: the app programmer does not have to log errors.
-
Small API: Squalid just binds variables to, and executes, prepared statements, so there is little to learn.
-
Simple API: You will probably never need to look at the manual. The API is too simple and obvious.
Other wrappers are more popular or cover more database vendors. The reasons to use Squalid instead are that you have to write a tiny fraction of the code, and that it checks for many errors.
Example problem: select a row from the database, with 38 fields: a NOT NULL and a "may be NULL" for each supported type of signed and unsigned integer, floating-point number, string, blob, and time point, bool, and variety of enum. Iterate over results, and confirm that there is only one row. How much code does it take?
| Approach | Code Size | Notes |
|---|---|---|
| Squalid | 12 lines | hand-written |
| mysqlclient library | 883 lines | ChatGPT-generated, some hand editing. Does NOT do as much error checking as Squalid (e.g., that integers actually fit in their target fields). |
| MySQL Connector/C++ | 186 lines | ChatGPT-generated, hand-edited. Does NOT do as much error checking as Squalid (e.g., that integers actually fit in their target fields). |
| SOCI | 298 lines | ChatGPT-generated, hand-edited. Does NOT do as much error checking as Squalid. As I was in a hurry, I actually ended up writing some wrapper functions on top of SOCI to have apples-to-apples error checking, so just using the wrapper straight would have been yet more code. |
Counting methodology: line count does not include explanatory log messages, comments, blank lines, and generally, multiple statements per line were not used. Lines are 100 characters or so long. Code was written to reflect how production code would be written for legibility. While it would result in yet shorter code, I did not want to actually write wrappers on top of these wrappers. This is a comparison of wrappers, not of wrapper wrappers.
Obviously, line count has a less than perfect correlation to the time required to author and maintain code. However, it usually has a strong correlation. If you are not convinced, look at the included test program, which includes these four implementations, and judge yourself the manpower required to author and maintain the code.
Reader Challenge: by all means, feel free to optimize the alternative implementations if you know them well, and I would be happy to amend this paper. I am certain they can be substantially improved. I have little experience with these APIs other than writing this comparison.
-
The massive reduction in code for the app programmer to write results in far less room to introduce bugs in the first place.
-
Embedding user input into SQL statements can result in SQL injection bugs, which can for instance allow a website user to wipe out or steal your database. Squalid makes it easier to bind parameter variables than to generate SQL text with user input in it, so the temptation to dangerously generate SQL text is much reduced.
-
Every libmysqlclient function call return value is checked for errors.
-
The error flags in MYSQL_BIND are always checked.
-
Every mysql_stmt_execute() call is followed by a check of mysql_warning_count() or mysql_stmt_warning_count(), and, if there are warnings, a query of SHOW WARNINGS.
-
Squalid refuses to bind non-optional<> variables to NOT NULL columns or other results. The is_null flag thus cannot be accidentally overlooked.
-
It is an error to bind integer to columns or other results whose value might not fit. If you have an unsuitable type, it will fail loudly in test, rather than perhaps passing in test and failing in production. For example, you cannot bind a 4-byte UserID parameter to a TINYINT column, and have the test succeed anyway due to a coincidence that test system user IDs are <256.
-
It is an error for the number of bound parameters or result fields to mismatch.
-
It is an error to bind any field to a C++ variable that is not a suitable match: a double column to an int64_t, for instance. (Integers can bind to any integer that can hold all possible values. float values can be placed in double destinations. Anything can be placed into a std::string. Unsigned 32-bit ints can be put in non-scoped enums on Windows. All other possibilities will be a certain fail the first time you try running it, whether the data happens to fit or not.)
- "An error" means a comprehensive message is logged, showing the failed SQL and detailed text explanation. Further use of the prepared statement is blocked.
- Structs for parameters and results are always filled in exactly right for the given variable type, including type, size, unsigned flag, and null flag.
- Text and Blob result variables automatically expand and cannot overflow or truncate.
-
RAII avoids resource leaks.
-
A test suite tests every line of code in Squalid (excepting a few rare errors such as out-of-memory). This was verified with gcov.
-
Squalid was analyzed with the static code analyzer cppcheck, and the Microsoft C++ Core Guidelines Checker, which report no issues at all.
This list is not exhaustive, but it is necessary to point out that despite the long list of preventative checks Squalid does for you, it is absolutely not doing the whole job. The following list is a very brief checklist of thing to consider, many of which do not have to do with Squalid per se, other than just to clarify that Squalid does not or cannot take care of these safety issues.
- It is still technically possible to build a statement with, say, string operations, that contains user input or other unvetted data, and allows an "SQL injection" which would allow some rando on the internet to delete your database or steal your customers' data.
- NEVER build SQL statements that include user input, random file contents, or even database contents, whether for queries or inserts. In fact, do not build them full stop. Even if data is coming from the database, it may have gotten there with a prepared statement, contain an SQL injection, and in fact be a time bomb waiting to happen.
- You must verify that the order of your SQL statement parameters or results matches the order of the bindings. Squalid does check for the number of bindings. If they don't match, Squalid logs a warning and refuses to execute. Squalid also checks types, so can spot transpositions of unlike-typed bindings in many cases. However, it cannot protect you from, for instance, having the wrong order of compatible fields.
- Assume that any edited binding has a field transposition and verify the ordering before you even begin acceptance testing.
- Error checking is much reduced: you only have to check for errors at the end.
- However, you still have to check for errors at the end!
- Squalid cannot check whether a std::string variable would be safe to bind as a parameter for a varchar(x) column. A std::string has no maximum size that would allow us to detect this. We can only police the actual values that are inserted. Since it is not possible to check what are surely the vast majority of inserts into varchar columns, Squalid does not bother checking whether the rare cases of an integer or other kind of data being inserted into a varchar field would fit either.
- You must manually verify data will fit into a text column. Squalid will inform you that something was truncated, and treat that as an error, but it is very possible such bugs will be data-dependent and not show up in testing.
-
Bound parameters must not change, currently, between calling BindParams() and Execute() or Next(). The value of a bound variable at either of those time points might be used, and this might change in future releases without notification.
-
Failure to consume all data by the time a statement is destroyed is merely logged as a trace message, not a warning. This is because there are bona fide reasons to do this.
-
Do not store raw passwords (e.g., from internet users) in any database, instead hash them. Even someone hacking you should not result in leaked passwords.
-
Set STRICT_TRANS_TABLES mode (see the test program for an example). If an insert or update would result in truncated or otherwise corrupted data, this flag tells the database to give an error instead of doing the corrupting operation then warning about it. Squalid will treat the message as an error either way, so the difference is only whether your database gets bad data in it or not. New projects should always use this setting (see tsSqualid.cpp for an example) but legacy software may actually use this bad data in their workflow and cope with it, and thus not be able to run with this flag.
-
Lower Development Costs and Faster Time To Market: far less code to write; less room for bugs to introduce themselves; does the logging for you; no library to install as header-only
-
Less Downtime: due to safety checks, should have fewer bugs; comprehensive logging should allow problems to be diagnosed quickly
-
Needs Less Hardware: automatically employs the highest-performance options from the menu, namely prepared statements with stored responses
-
Lower Support and Handover Cost: since there's far less code, there's far less to read and understand; maintainers shouldn't need expertise in the MySQL/MariaDB APIs
The file hwSqualid.cpp is a live example that executes DROP, CREATE, INSERT, UPDATE, SELECT, SELECT COUNT(*), and DELETE statements, though of course Squalid isn't limited to this range. It illustrates each of the 38 supported C++ data types. One could probably cut and paste from this and get working production code, but database interfacing is very serious work where bugs can allow hackers to delete or steal your data, so it is recommended that you read this entire document carefully anyway.
The file tsSqualid.cpp is the acceptance test and will become the regression test for any bugs found. Additionally it performs a performance test vs. the raw API, Connector/C++, and SOCI. The line counts of these different versions of the same function further comprise the line count comparison discussed in this document. Finally, it has a hammer test, running the test for many hours, demonstrating stability and lack of memory leaks.
Both files start with a comment showing how to build them on Linux/Unix. Building on Windows is more involved. Rather than ship with Visual Studio files, this section documents how to incorporate this software in your own projects, and you can follow that to build the example software.
The exact test machines' OS and compiler versions are in this document in a separate section if needed.
The following are only used by the example apps, not by Squalid itself.
If you get errors about SSL with MariaDB client libraries on Windows, add /D SqualidMariaDBDisableSSL .
If you get errors about plugin directories with MySQL client libraries on Windows, add /D SqualidMySQLPluginDir="T:/downloads/MySQL/mysql-9.5.0-winx64/lib/plugin" or similar.
See the one-line build command in the source. It has been tested with C++20, 23, 26 with g++ and clang++, and with –g and –O2.
The example compilation line includes several -W options, with which no warnings or errors are currently output. These are not necessary for correct compilation, however.
Make a workspace, then an empty project. (This explanation assumes you make this workspace in the directory you put the Squalid distribution in.)
Add hwSqualid.cpp under Source Files. It should be in ..\..\.. which is to say three directories up.
Set PropertiesDebuggingEnvironment to something like the following; you will need the MariaDB or the MySQL paths (not both, just depending on which DB you are using) so that at runtime, various hot-pluggable security libraries can be found: PATH=C:\Program Files\MariaDB\MariaDB Connector C 64-bit\lib;T:\downloads\MySQL\mysql-9.5.0-winx64\bin;T:\downloads\MySQL\mysql-9.5.0-winx64\lib\plugin\%PATH%
Set Properties-->Debugging-->Command Arguments to something like the following: 192.168.20.1 MyTestUser MyTestDB
Set Properties-->C/C++-->General-->Additional Include Directories to something like the following (using MariaDB or MySQL depending on which you are using): ..\..\..;C:\Program Files\MariaDB\MariaDB Connector C 64-bit\include;T:\downloads\MySQL\mysql-9.5.0-winx64\include
Set Properties-->C/C++-->Preprocessor-->Preprocessor Definitions to the following, if using MariaDB and you don't need SSL: SqualidDisableMariaDBSSL;_DEBUG;_CONSOLE;%(PreprocessorDefinitions)
Set Properties-->C/C++-->Language-->C++ Language Standard to c++20, c++23preview, or c++latest.
Set Properties-->Linker-->General-->Additional Library Directories to something like the following (you only need MariaDB or MySQL, depending on which client library you are using): C:\Program Files\MariaDB\MariaDB Connector C 64-bit\lib;T:\downloads\MySQL\mysql-9.5.0-winx64\lib
Set Properties-->Linker-->Input-->Additional Dependencies to something like the following: libmariadb.lib;$(CoreLibraryDependencies);%(AdditionalDependencies)
Additionally, it is common to set C/C++ and Linker's General Suppress Startup Banner to No, so you can see the commands and solve any issues.
Cut and paste the build command from the comment at the beginning.
Start by studying main() . It shows connecting to the database; running drop, create, insert, update, select, and delete statements; and closing the connection.
The SqualidLog() function must be defined by your application. The test program tsSqualid.cpp has a fancy one with timestamps and code locations. The hello world hwSqualid.cpp simply outputs the actual log text via cout.
Everything else is just boilerplate, portability, or convenience.
Supply your database server address, user, and database name as command line arguments.
You will be prompted for the password with console echo disabled.
There is one public class, with about six methods. This wraps the "prepared statement" which suffices for all operations for some apps, and probably a majority of operations for most apps. (Additional features will be added as requests and use cases are reported.)
Squalid is not (yet?) a compatibility shim, so the user will use the plain old MySQL library calls to establish and break connection and to do any operations not possible with prepared statements.
See the "Hello World" program hwSqualid.cpp for a running example.
The entire library uses namespace Squalid. A few macros are defined, all of which are prefixed Squalid .
These allow you to create and drop tables; insert update and delete data; and run select queries.
Constructor. Don't bother checking Error().
BindParams() and BindResults(). Do not bother checking return code.
Execute() only for statements that don't have results you'll process with Next(). Do not bother checking return code.
while ( Next() ) until it returns 0.
if ( Error() ) will be non-zero if there were any errors along the chain, whether constructor, binding, execution, or processing the result set. You can call ErrorText() if you need to have a description of the error, but the errors will already have been logged. The string is formatted for the engineer to read, not for automated processing.
PreparedStmt stmt( MYSQL* pconn, const std::string& sSQL )
pconn A live connection to a MySQL or MariaDB database, created with mysql_init(), mysql_options(), and mysql_real_connect().
sSQL The SQL command to run. To avoid SQL injection attacks, never build this string with string operations. It should be a literal string in most cases.
The main headache is that for selects, you will have to get the right number of question marks to hold the bound parameters.
The destructor is implicit and implements the RAII paradigm: resource leaks are not possible.
If you need to, you can check for errors now, with Error(). However, you can just assume it worked, and continue on to bind variables, execute and loop over any results and only at the end check the Error() indicator. Once a prepared statement has some error, subsequent operations are simply "no-op" and do not harm anything or generate further errors.
int stmt.BindParams( variable [, variable ] )
int stmt.BindResults( variable [, variable ] )
variable A list of one or more variables that will be passed by reference (if lvalues) or value (for BindParams(), if rvalues) and bound to the statement, as parameters and to hold results.
The return value is 0 for success, non-zero for failure. However, you typically will not bother checking at this point. Even if the constructor or these methods have an error it's OK to proceed to the Execute() and Next() steps, and after finding that there's no (more) data to obtain, check Error() one time at the end.
An "insert" command will probably have quite a few parameters, one per column. A "select" command will usually have several variables bound to hold the results, but additionally perhaps have some parameters for the "where" clause.
Do not change the values of variables you set with BindParams() until you call Execute() or Next(). You can pass in both lvalues (variables) and rvalues (computed values and constants). For the sake of efficiency, Squalid does not make an internal copy of lvalue parameters, instead taking them from the variables when Execute() or Next() is called.
int stmt.Execute()
The return value is 0 for success, non-zero for failure. However, you typically will not bother checking at this point. Even if the constructor, the binding step or Execute() resulted in an error, it's fine to proceed to the Next() step, and after finding that there's no (more) data to obtain, check Error() one time at the end.
Next() calls this automatically if it hasn't been called. It is suggested that for selects, don't bother with Execute() and just call while( Next() ) until the looping finishes. If there won't be results, however, call Execute().
int stmt.Next()
The return value is 0 for "no more data" or "some error was detected," and, non-zero for "we obtained another row without error." This method by itself will not inform you whether there is an error condition, or whether the data simply is finished, but you should not need to know. Once Next() returns 0, always check Error(). If there is an error, you are guaranteed of a 0 return value on Next() and non-0 on Error(). If there is no error, you are guaranteed of getting a 0 return value on Next() if and only if you have consumed all the rows and are asking again, and a 0 return on Error().
int stmt.Error()
std::string stmt.GetError()
The return value for Error() is 0 for success, non-zero for failure. GetError() will always return a string suitable for logging, but remember, it will also already have been logged. Therefore, it is reasonable and even expected that a fully productionized application may never need GetError(), as long as your implementation of SqualidLog() is such that developers will see the message. If Error() is 0, the GetError() return value will contain the SQL statement and a note that there were no errors.
If there is an error it is logged immediately, and further calls to methods on that prepared statement will gracefully do nothing. So, you only need to call Error() at the end of this chain of operations. Since the error message will already be logged, you probably don't need to refer to the exact text of the error message, but if you want it, you can call GetError(). It is expected that in most programs, if there is an error, the app will either exit, or signal support for help, or dump core, or simply be satisfied the error is logged and continue. Most applications, it is assumed, probably do not need to know what the error was, and do not even to know whether it was at the construction, binding, execution or iteration step.
int stmt.StreamResults()
The return value for Error() is 0 for success, non-zero for failure. However, you do not need to check immediately. Even if there's an error, you can continue to attempt to Execute() or loop over results with Next() and defer error-check to the end. At the moment this method will always succeed in and of itself and only reports error if there was already an error (e.g., in construction or binding).
By default, Squalid uses mysql_stmt_store_result() to get an entire result set in one IPC call to the server, instead of a round trip per row. For one-row data sets (quite common—queries for unique keys etc.) there is no overhead for this, and any time you are consuming all rows it is going to be fast and potentially orders of magnitude faster.
However, for rare cases where you may only be consuming some of the results, it could potentially be orders of magnitude slower (e.g., a select returns 10,000 rows of which you only want the first one).
This is not currently supported. Make a new one every time you do an insert, select, etc. While it is very slightly inefficient to do startup, and the Bind…() methods could in theory reload new variables more efficiently than making initial bindings, the fact that we're taking an IPC round trip means the savings are typically very insignificant.
One of the earlier future releases will probably support this, allowing you to put Squalid::PreparedStmt inside other classes or even make them file-scoped; initialize once; then simply re-call Bind…() as needed. Wall clock time will often be unchanged, but CPU used may be cut drastically.
If you need to do something the above API does not accomplish, you are free to simply use the database API directly. Squalid does not care if you intermix Squalid calls with direct library calls.
You may also wish to report your needs in the Facebook group Squalid API and perhaps your needs can be addressed in the next or a future release.
Should you have to check, the following defines should be self-explanatory. Unlike many systems, the major release number does not necessarily indicate a huge change, but rather a source incompatibility. It is possible a full rewrite will merely qualify as a Novel Feature or even Bug Fix, while fixing a typo in a function name could result in an Incompatible Change.
All changes should be assumed to be binary-incompatible, meaning you should not attempt to mix object files compiled with two versions of Squalid into the same binary. It may work fine in most cases, especially if objects are not shared between object files built with different versions, but the safe recommendation is: don't.
#define SqualidIncompatibleChange 1
#define SqualidNovelFeature 0
#define SqualidBugFix 0
#define SqualidVersion ((SqualidIncompatibleChange)*1000000+
SqualidNovelFeature)*1000+(SqualidBugFix))
Download and unzip mysql-9.5.0-winx64.zip (or preferred version) from https://dev.mysql.com/downloads/mysql . You will be asked to use or create an Oracle login but below there is currently a link for "No thanks, just start my download."
Typically headers in an SDK like this are in a branded directory (e.g., mysql/) so that #include statements can specify a header in this SDK, not some other SDK with the same name. However, Oracle is not doing that here, so the Hello World and test programs must check OS and include either mysql.h for Windows, or mysql/mysql.h for Linux. (I would suggest they put them in a branded directory going forward.)
MySQL only distributes libraries built to link against the release version of the Microsoft runtime libraries. You will get errors like error LNK2038: mismatch detected for '_ITERATOR_DEBUG_LEVEL': value '0' does not match value '2' if you are having this problem. You cannot compile with /MDd or /D _DEBUG . Instead, you are limited to /MD. You can build for release, or change your project properties. You can, in theory, hand-build MySQL debug versions but it is a many-hour process (apparently). Note the limitation is whether the runtime has debug features, not whether you compile with the debug flag. I switched to release but turned on the debug flag and can debug my software fine. To summarize:
-
For libmariadb.lib, you can use any combination of /MD, /MDd and /D _DEBUG or not.
-
For mysqlclient.lib, you cannot use /MDd or /D _DEBUG , or you get LNK2038. /MD works.
If you get errors of Authentication plugin 'mysql_native_password' cannot be loaded, search the code for MYSQL_PLUGIN_DIR and edit that line to point to your plugin directory.
Some DLL's will be loaded at runtime. To allow them to be found, copy the files it complains about to the .exe's directory, or add their directory to PATH. You can also add them to a debugging path in Properties-->Configuration-->Debugging-->Environment: PATH=T:\downloads\MySQL\mysql-9.5.0-winx64\bin;T:\downloads\MySQL\mysql-9.5.0-winx64\lib\plugin\%PATH%
Squalid was originally built in a software ecosystem that had a comprehensive high-performance logging module. Rather than supply that with Squalid, the header Squalid.h is very slightly hacked to allow you to pre-define your own logging wrapper, which will be called when Squalid has trace or warnings to log. You can copy the boilerplate extremely simple wrapper from the Hello World program, but in short:
-
Define SqualidStandaloneLogging with a #define or –D .
-
Define the following function. It can log, call other functions, return without doing anything, whatever you want. It will get the file, function, and line number of the caller, a single letter severity, and a text string with the actual message in it. The severity will be T for trace (can be ignored usually), S for status (basic logging), W for warning (should not happen), E for error (the program cannot and must not continue) and F for fatal (the same).
void SqualidLog( const char* pszFile, const char* pszFunc, int iLine,
const char cPrefix, const char* pszAppMsg = "")
You can bind the following variable types:
-
int8_t, int16_t, int32_t, and long long, and their unsigned variants
-
float and double
-
std::string (both for text and blob fields)
-
time_point
-
bool
-
enum and enum class inheriting from every integer size.
If a result column might have NULL values, you must bind a std::optional<> of the above variable type. (If Squalid let you write code that assumes there will be a value, when there may not be, you have the danger of code working on a test system but later failing in production.)
Whether binding a C++ integer variable parameter to a database column, or binding a database column to a C++ integer result:
-
If source and destination are the same signedness, the destination size must be equal or larger.
-
If source is unsigned and destination signed, the destination size must be larger.
-
If the source is signed and the destination unsigned, Squalid will not let the binding work.
In short, the destination must be able to hold all possible values of the source.
This may seem draconian or inflexible, but the reason is that if Squalid allowed smaller destinations, then depending on test data, you might have code that works in test but fails in production. For instance, binding a 32-bit unsigned user ID to a 16-bit signed int will pass tests if you do not have test user ID's over 32767. This binding feature of squalid is meant to assure that you will not have this kind of problem in test and not know about it. (Instead, like all other issues, it will be logged and the bound statement will not execute.)
Squalid supports C++ enum class for 8, 16, 32, and 64-bit signed and unsigned values. 8 and 16-bit enums can be bound to ENUM columns, or to TINYINT and SMALLINT. The other must bind to INT and BIGINT. More specifically, they follow the same binding size rules as a like-sized integer (see above). This ensures that whatever the contents, it will fit in the destination, whichever side is the destination.
MySQL auto-numbers enums gaplessly from 1. C/C++ enums number by default from 0. If you define a database column as ENUM, you have to define your c++ enum such that the first value is 1 and from there continuous. This will then show your enumeration values as text in a query, which may be valuable or critical. Alternatively, if you need more flexibility as to how you define your C++ enum, consider making the database column an integer the same size and signedness.
MySQL lets you bind C++ enum parameters to ENUM columns. In other words, it accepts in integer format something it stores internally as an integer. However, when you query such a column, the MySQL result will be the text value you assigned. This means: you can bind an ENUM column to an std::string result, but not to an actual enum or enum class! Your workarounds are:
-
If you have enums that cannot be defined 1, 2, 3…, make the database column the same integer type as your enum class. For instance, define a column meant to hold an enum class: uint8_t as TINYINT UNSIGNED NOT NULL. You can then bind your enum class: uint8_t variable as both a parameter and as a result. This is the simplest from the C++ developer's perspective. The downside is that other database users will not be able to see the enumeration text instead of the number.
-
In your MySQL table, add a virtual column that is the type of your variable (e.g., TINYINT UNSIGNED for an enum class: uint8_t:
e8 ENUM('A','B','C') NOT NULL,
e8num TINYINT UNSIGNED AS (e8 + 0) VIRTUAL NOT NULL,This lets you have your numeric result the C++ programmers like, while also having the enumerated text other database users may rely on.
Note however that VIRTUAL only supports NOT NULL starting in MariaDB 11.0.1 and MySQL 5.7. If you do not have that, leave out the NOT NULL, but then Squalid will insist that you bind to an optional<> integer or enumeration. This should have no performance impact: virtual columns do not consume disk space, memory, communication bandwidth, backup space and time, etc. -
Accept the MySQL weirdness. Write into the database from your enum class variable, but read from the database into an std::string. Maybe you can actually use it as such, or have a bank of if ( s == "foo" ) e = E::Foo; statements or map<> lookup.
-
In your select statement, write CAST(e8 AS UNSIGNED) or (e8 + 0) (for a column called e8) to get an UNSIGNED INT . These work if you have a 4-byte enum, but often you do not want a 4-byte enum. There is no way to make MySQL return a smaller integer from a cast.
-
As #4, but compile with –DSqualidEnumAnySize. This allows any size enum to bind to any size database column both ways. Your downsides are the lack of size check safety, and writing the hacky cast or math in your query. You still have the enumeration text to support other database users, and do not complicate the CREATE script with program-specific hacks.
In practice, GNU g++ and clang++, when running on Windows 95 or Linux on x86 or Apple's ARM64, define enums as 32-bit unsigned int. These are therefore treated exactly as an enum class: uint32_t, above.
However, Microsoft Visual C++ defines enums as 32-bit signed int. This is a problem because MySQL returns unsigned ints. One alternative was to forbid Windows and any portable software from reading enum fields into unscoped enums. Another was to require the user use the hacky –DsqualidEnumAnySize compile flag. Instead, it was decided to make one slight exception to the general rule that all possible source values must be able to fit in the destination, whether copying to or from the database. This is to allow unsigned MYSQL_TYPE_LONG unsigned fields to be read into signed 32-bit ints without error or even a trace message.
In theory, the C++ specs do not promise what this size will be and leave it as implementation-defined, so it is just theoretically possible that this will not be portable to an unusual architecture. If this happens, just figure out what size is used and use the same, or use the hacky –DsqualidEnumAnySize flag when you compile your code.
The application programmer needs to have the maximum of information about errors so they can figure out exactly what happened with the minimum of outages. This benefits TCO (Total Cost of Ownership) by lowering support/maintenance and increasing uptime.
However, as distinct from the application programmer, the application itself usually does not care what the error was. Something did not work, so the app alerts support staff, or shuts down operations, or whatever. In fact, the application does not even usually care whether the failure was at statement preparation time, binding, execution, or processing results. Reducing the amount of involvement by the app in handling errors benefits TCO by lowering development time, and the amount of code that needs to be studied and understood at handover time.
To serve the application programmer, Squalid logs all available information: function return codes, per-column flags, and any available WARNINGS, in addition to internally detected errors. It logs timestamps, file locations, and function names where the error is occurring. It aims for the logging gold standard of: the error message should be detailed enough to maximize the number of bugs that can be understood and fixed with one production occurrence.
To reduce application programmer workload, though, the application itself need not check for errors at every step, examine error codes, or log anything. You can prepare a statement, bind parameters and results, and loop over the results without checking for errors at every step unless you want to. You can defer the check to the end: after looping over results, you see there is an error. It probably does not matter where, exactly, or what, and you do not have any boilerplate logging to do. Just based on a boolean status method Error(), the app can just return, alert support, shut itself down or whatever.
Example code: this actually does full error checking at every point and logs the error fully. Even if the error is in the prepared statement creation, or the binding, you do not need to stop at every stage and check. The problem is that the 16-bit iRows is not big enough to always hold a table row count, so the binding failed. (It does not matter if this time the value happens to fit. Squalid refuses to let you continue. This is critical for finding bugs during test, rather than in production.) Yet we do not have to check the error code at the binding step.
int16_t iRows;
SqlStatement stmt( pconn, "SELECT COUNT(*) FROM test_bindings" );
stmt.BindResults( iRows );
stmt.Next();
if ( stmt.Error() )
return;
The actual error text is available via ErrorText() if you need it. However, the following is already logged for you, so often you will not need anything more:
W 251204 16:00:03.463 Squalid.h:423 BindResult(): Column #1 COUNT(*) is LONGLONG; bound int of same sign must be equal or greater size but is int16_t
The server was run on, and the library was tested on, an i7-7700HQ-equipped 32GB ThinkPad T470p running Fedora 40. The same computer is running MariaDB and the test program.
Running g++ -v reports:
Using built-in specs.
COLLECT_GCC=g++
COLLECT_LTO_WRAPPER=/usr/libexec/gcc/x86_64-redhat-linux/14/lto-wrapper
OFFLOAD_TARGET_NAMES=nvptx-none:amdgcn-amdhsa
OFFLOAD_TARGET_DEFAULT=1
Target: x86_64-redhat-linux
Configured with: ../configure --enable-bootstrap --enable-languages=c,c++,fortran,objc,obj-c++,ada,go,d,m2,lto --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-bugurl=http://bugzilla.redhat.com/bugzilla --enable-shared --enable-threads=posix --enable-checking=release --enable-multilib --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-gnu-unique-object --enable-linker-build-id --with-gcc-major-version-only --enable-libstdcxx-backtrace --with-libstdcxx-zoneinfo=/usr/share/zoneinfo --with-linker-hash-style=gnu --enable-plugin --enable-initfini-array --with-isl=/builddir/build/BUILD/gcc-14.0.1-20240411/obj-x86_64-redhat-linux/isl-install --enable-offload-targets=nvptx-none,amdgcn-amdhsa --enable-offload-defaulted --without-cuda-driver --enable-gnu-indirect-function --enable-cet --with-tune=generic --with-arch_32=i686 --build=x86_64-redhat-linux --with-build-config=bootstrap-lto --enable-link-serialization=1
Thread model: posix
Supported LTO compression algorithms: zlib zstd
gcc version 14.0.1 20240411 (Red Hat 14.0.1-0) (GCC)
Running gcov -v reports:
gcov (GCC) 14.0.1 20240411 (Red Hat 14.0.1-0)
JSON format version: 2
Copyright (C) 2024 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
The test program is built with libmysql 8.0.36 .
Running mysql –v reports:
mysql Ver 15.1 Distrib 10.11.11-MariaDB, for Linux (x86_64) using EditLine wrapper
The library was tested on an i7-11700F-equipped 32GB H-P Envy TE01-2xxx running Windows 11 Pro 25H2. The above Linux box ran the MariaDB server for this development and testing as well.
The software was developed and tested with Microsoft Visual Studio Community 2022 (64-bit) – Current, Version 17.14.15 (September 2025).
One always wants to write efficient code, and avoid the copying around of data unnecessarily. It is "obvious" that one should read string data from the database directly into the std::string that will hold it, rather than reading into an old-fashioned C malloc() buffer and copying it, but it no way to do this was found.
When we call MySQL to execute a prepared statement, we must provide arrays of small structures that describe each field of the parameters and of the results.
We have to handle in theory eight situations, with all combinations of 1) lvalue or rvalue argument, 2) no null or potential null (which Squalid mandates use of optional<> for), and 3) parameter or results. In practice, rvalues are transient and do not exist long enough to hold return values, so we have six cases to support.
| Use | Arg | Style | Notes |
|---|---|---|---|
| param | lvalue | non-null | we could point to app variable's data(), or make our own copy |
| param | lvalue | maybe-null | we could point to app variable's data(), or make our own copy |
| param | rvalue | non-null | must make its own copy as caller's ceases to exist |
| param | rvalue | maybe-null | must make its own copy as caller's ceases to exist |
| result | lvalue | non-null | must make our own copy as we cannot read into std::string |
| result | lvalue | maybe-null | must make our own copy as we cannot read into std::string and further, optional<string> may not even hold a string to read into: access is undefined unless it has_value() |
| result | rvalue | non-null | illegal: rvalue ceases to exist so can not store into them |
| result | rvalue | maybe-null | illegal: rvalue ceases to exist so can not store into them |
While you can find an std::string capacity() and get the address of its data() and read into that, you can not resize() to hold the number of characters read unless the string was previously bigger. While you can artificially pump strings up to their capacity, have MySQL write into their data() area, and resize() them smaller, it's inefficient to do so. (A non-problem is that the capacity() might be too small; in this case you can simply reserve() more storage and try again, same as with a malloc()'d buffer.)
So, this library holds these strings the old-fashioned way: in malloc()'d buffers that are realloc()'d if they prove to be too narrow. The buffer is not null- (or zero-) terminated: MySQL does not provide such termination, and std::string does not require it, so we do not bother terminating in the mean time.
See the file TODO.txt for open programming issues.
Half the utility of Squalid is to use MySQL and MariaDB databases from modern C++. However, half the utility is a proof of concept for the API design itself. The API could be replicated to create versions for other databases, or a database-agnostic API, or made part of other wrappers and toolkits.
Currently only prepared statements are supported, and at that only a small portion of the available API. The portion supported seems to be sufficient for the pilot use case but with certainty will need expansion going forward. Rather than guess what is needed, instead feedback and requests will drive future editions.
Only a very specific range of types is covered. Other combinations could be supported.
The emphasis was on binding variables to prepared statements for create, insert, update, select, delete, and drop, but could be extended to cover the rest of the MySQL API.
More static and dynamic code analyses are always welcome.
SQL_TYPE_TIME, SQL_TYPE_DATE, SQL_TYPE_INT24, and other potentially useful types are not yet supported.
SQL_TYPE_TIME may be best served by binding to a vector<byte> instead of a std::string.
Character sets (charsetnr) are not tested.
It might be a good idea to have a simple state concept to prevent attempts to BindParams() or set Stream() after Execute(), etc.
| Version | Changes |
|---|---|
| 1.0.0 | Initial release. |
MySQL and MariaDB are in most ways compatible projects. API is more or less the same, differing more by release number than between projects. You can use either client library to talk to either server. MySQL is owned by Oracle while MariaDB is an open software version by the same initial developers. You can google up for more info on the differences, but from the standpoint of demoing or even production use of Squalid, either should work.
One problem with this is that they are so similar that the MariaDB packages install into the same directories on Linux, even though they're not quite the same thing, and this is the source of some headache if you're trying to switch between them. I would suggest that if you have one, just try out Squalid with that rather than try to switch.
Some Linuxes may ship with one or the other already installed. If not, you can install MariaDB with:
sudo dnf install mariadb-plugins
sudo dnf install mariadb-server
On Linux: sudo mysql -u root . On Windows, run MySQL Client from the Start menu and supply root credentials. You should now see MariaDB [(none)]> or mysql> .
To test, we will need a database. Create one with:
CREATE DATABASE SqualidDemo;
Verify with:
SHOW DATABASES;
We need a user to run the test with. Do not test with root! Instead:
CREATE USER 'squaliduser'@'%' IDENTIFIED BY 'mypassword';
Or, to limit access to users on the same machine:
CREATE USER 'squaliduser'@'localhost' IDENTIFIED BY 'mypassword';
Finally, allow the test user to use the test database:
GRANT ALL PRIVILEGES ON SqualidDemo.* TO 'squaliduser'@'%';
FLUSH PRIVILEGES;
At this point you should be able to compile and run the test program tsSqualid .
I am relatively new to MySQL and found some points that really do not make sense to me, though it is possible there are good explanations:
-
The Fedora packages install MySQL and MariaDB under /usr/include, /usr/bin and so on, which prevents you from running multiple packages in parallel. They're making the simple case (systems with only one version) a bit simpler, by avoiding the need to add –I and –L compilation options and adding something to your $PATH and $LD_LIBRARY_PATH. However, they are making the difficult case far more difficult. I don't see how one can have a complete set of MySQL and MariaDB versions side by side to regression test my code on all versions, unless one compiles them all oneself.
-
When a database float or double column is bound to a MYSQL_TYPE_STRING buffer type, it silently accepts whatever buffer size you give, not just rounding decimal places but also literally changing the number by orders of magnitude. A float of 123.5 will simply give you 12 if you have buffer_length=2. I cannot imagine why the library does not simply negotiate a bigger buffer, as it does for strings and blobs and seems to do for all integer types and time points. The relevant document is https://dev.mysql.com/doc/c-api/8.4/en/c-api-prepared-statement-type-conversions.html .
-
The type of lots of functions seems to be long long or unsigned long long. I suggest a type like MYSQL_RV should have been defined, much as Standard C/C++ use size_t.
-
mysql_stmt_affected_rows() is documented to return a -1, despite having an unsigned return type. Rather than force the user to perform casting or perform bit math, there should be a value like Standard C's EOF: MYSQL_ROWS_ERROR or some such.
-
mysql_close() lacks a return value which is mysterious.
This information was gathered long after the install so is certainly incomplete, but may provide some clues for other users:
sudo dnf install mysql-connector-c++-devel
sudo dnf debuginfo-install mysql-connector-c++-jdbc-9.3.0-1.fc40.x86_64
sudo dnf debuginfo-install mysql-connector-c++-jdbc
sudo dnf info mysql-connector-c++-jdbc
This information was gathered long after the install so is certainly incomplete, but may provide some clues for other users:
sudo dnf install soci-devel soci-mysql-devel
sudo dnf install boost-devel
This information was gathered long after testing so is certainly incomplete, but may provide some clues for other users:
lcov --capture --directory . --output-file coverage.info --rc branch_coverage=1 --ignore-errors unused --exclude '/usr/*' --exclude '/usr/include/*'
genhtml coverage.info --output-directory coverage-html
This information was gathered long after the testing so is certainly incomplete, but may provide some clues for other users:
sudo dnf install cppcheck
clear ; cppcheck --enable=all --std=c++20 --inconclusive --force Squalid.h tsSqualid.cpp --suppress=missingIncludeSystem --check-level=exhaustive