You Can’t Escape Quotes



SQL expression support is easy to use when modifying Windows Installer packages, but there is one major drawback that continues to cause problems: you can’t escape single quotation marks in Windows Installer SQL expressions. Consider the following sample code:

LPCTSTR pszExample = TEXT(“Heath ‘ClubStew’ Stewart”);
_stprintf_s(szSQL, MAX_SQL, TEXT(“INSERT INTO `Property` (`Property`, `Value`) VALUES (‘Example’, ‘%s’)”), pszExample);
uiRet = MsiDatabaseOpenView(hDatabase, szSQL, &hView);

uiRet would be set to ERROR_BAD_QUERY_SYNTAX. This commonly occurs when working with French and Italian text, which may often contain apostrophes. It may certainly occur in other texts, such as in English to show possession. Whatever the case, if your APIs are accepting unknown input for use in modifying Windows Installer packages, you’d best consider one of two approaches documented in the Windows Installer SDK.

If you’re code is already heavily invested in SQL expressions, you can make a relatively easy change and use query parameters – using question marks (?) – and pass an MSIHANDLE to a record that contains the values to replace. Let’s modify the example above slightly:

LPCTSTR pszSQL = TEXT(“INSERT INTO `Property` (`Property`, `Value`) VALUES (‘Example’, ?)”);
LPCTSTR pszExample = TEXT(“Heath ‘ClubStew’ Stewart”);
PMSIHANDLE hRecord = MsiCreateRecord(1);
MsiRecordSetString(hRecord, 1, pszExample);
uiRet = MsiDatabaseOpenView(hDatabase, szSQL, &hView);
uiRet = MsiViewExecute(hView, hRecord);

Highlighted portions show the pertinent changes.

You may also choose to use the MsiViewModify() function. I prefer this function because you can select a view over a whole table and make modifications to the table in various ways based on the eModifyMode parameter. There is no need to create different SQL expressions to make different modifications. In the preceding example, consider what would happen if the property ‘Example’ already existed. The call would fail either way. Using MsiViewModify(), the API will react accordingly if we use MSIMODIFY_ASSIGN:

LPCTSTR pszSQL = TEXT(“SELECT `Property`, `Value` FROM `Property`”);
LPCTSTR pszProperty = TEXT(“Example”);
LPCTSTR pszExample = TEXT(“Heath ‘ClubStew’ Stewart”);
PMSIHANDLE hRecord = MsiCreateRecord(2);
MsiRecordSetString(hRecord, 1, pszProperty);
MsiRecordSetString(hRecord, 2, pszExample);
uiRet = MsiDatabaseOpenView(hDatabase, szSQL, &hView);
uiRet = MsiViewModify(hView, MSIMODIFY_ASSIGN, hRecord);

If you’re not sure if your input will contain apostrophes or not, it would be best to use one of the two previous methods to make sure your update scripts don’t fail in such an event.


Comments are closed.