diff options
Diffstat (limited to 'ext/icu')
| -rw-r--r-- | ext/icu/README.txt | 169 | ||||
| -rw-r--r-- | ext/icu/icu.c | 501 | ||||
| -rw-r--r-- | ext/icu/sqliteicu.h | 27 |
3 files changed, 697 insertions, 0 deletions
diff --git a/ext/icu/README.txt b/ext/icu/README.txt new file mode 100644 index 0000000..c5cadb5 --- /dev/null +++ b/ext/icu/README.txt @@ -0,0 +1,169 @@ + +This directory contains source code for the SQLite "ICU" extension, an +integration of the "International Components for Unicode" library with +SQLite. Documentation follows. + + 1. Features + + 1.1 SQL Scalars upper() and lower() + 1.2 Unicode Aware LIKE Operator + 1.3 ICU Collation Sequences + 1.4 SQL REGEXP Operator + + 2. Compilation and Usage + + 3. Bugs, Problems and Security Issues + + 3.1 The "case_sensitive_like" Pragma + 3.2 The SQLITE_MAX_LIKE_PATTERN_LENGTH Macro + 3.3 Collation Sequence Security Issue + + +1. FEATURES + + 1.1 SQL Scalars upper() and lower() + + SQLite's built-in implementations of these two functions only + provide case mapping for the 26 letters used in the English + language. The ICU based functions provided by this extension + provide case mapping, where defined, for the full range of + unicode characters. + + ICU provides two types of case mapping, "general" case mapping and + "language specific". Refer to ICU documentation for the differences + between the two. Specifically: + + http://www.icu-project.org/userguide/caseMappings.html + http://www.icu-project.org/userguide/posix.html#case_mappings + + To utilise "general" case mapping, the upper() or lower() scalar + functions are invoked with one argument: + + upper('ABC') -> 'abc' + lower('abc') -> 'ABC' + + To access ICU "language specific" case mapping, upper() or lower() + should be invoked with two arguments. The second argument is the name + of the locale to use. Passing an empty string ("") or SQL NULL value + as the second argument is the same as invoking the 1 argument version + of upper() or lower(): + + lower('I', 'en_us') -> 'i' + lower('I', 'tr_tr') -> 'ı' (small dotless i) + + 1.2 Unicode Aware LIKE Operator + + Similarly to the upper() and lower() functions, the built-in SQLite LIKE + operator understands case equivalence for the 26 letters of the English + language alphabet. The implementation of LIKE included in this + extension uses the ICU function u_foldCase() to provide case + independent comparisons for the full range of unicode characters. + + The U_FOLD_CASE_DEFAULT flag is passed to u_foldCase(), meaning the + dotless 'I' character used in the Turkish language is considered + to be in the same equivalence class as the dotted 'I' character + used by many languages (including English). + + 1.3 ICU Collation Sequences + + A special SQL scalar function, icu_load_collation() is provided that + may be used to register ICU collation sequences with SQLite. It + is always called with exactly two arguments, the ICU locale + identifying the collation sequence to ICU, and the name of the + SQLite collation sequence to create. For example, to create an + SQLite collation sequence named "turkish" using Turkish language + sorting rules, the SQL statement: + + SELECT icu_load_collation('tr_TR', 'turkish'); + + Or, for Australian English: + + SELECT icu_load_collation('en_AU', 'australian'); + + The identifiers "turkish" and "australian" may then be used + as collation sequence identifiers in SQL statements: + + CREATE TABLE aust_turkish_penpals( + australian_penpal_name TEXT COLLATE australian, + turkish_penpal_name TEXT COLLATE turkish + ); + + 1.4 SQL REGEXP Operator + + This extension provides an implementation of the SQL binary + comparision operator "REGEXP", based on the regular expression functions + provided by the ICU library. The syntax of the operator is as described + in SQLite documentation: + + <string> REGEXP <re-pattern> + + This extension uses the ICU defaults for regular expression matching + behaviour. Specifically, this means that: + + * Matching is case-sensitive, + * Regular expression comments are not allowed within patterns, and + * The '^' and '$' characters match the beginning and end of the + <string> argument, not the beginning and end of lines within + the <string> argument. + + Even more specifically, the value passed to the "flags" parameter + of ICU C function uregex_open() is 0. + + +2 COMPILATION AND USAGE + + The easiest way to compile and use the ICU extension is to build + and use it as a dynamically loadable SQLite extension. To do this + using gcc on *nix: + + gcc -shared icu.c `icu-config --ldflags` -o libSqliteIcu.so + + You may need to add "-I" flags so that gcc can find sqlite3ext.h + and sqlite3.h. The resulting shared lib, libSqliteIcu.so, may be + loaded into sqlite in the same way as any other dynamically loadable + extension. + + +3 BUGS, PROBLEMS AND SECURITY ISSUES + + 3.1 The "case_sensitive_like" Pragma + + This extension does not work well with the "case_sensitive_like" + pragma. If this pragma is used before the ICU extension is loaded, + then the pragma has no effect. If the pragma is used after the ICU + extension is loaded, then SQLite ignores the ICU implementation and + always uses the built-in LIKE operator. + + The ICU extension LIKE operator is always case insensitive. + + 3.2 The SQLITE_MAX_LIKE_PATTERN_LENGTH Macro + + Passing very long patterns to the built-in SQLite LIKE operator can + cause excessive CPU usage. To curb this problem, SQLite defines the + SQLITE_MAX_LIKE_PATTERN_LENGTH macro as the maximum length of a + pattern in bytes (irrespective of encoding). The default value is + defined in internal header file "limits.h". + + The ICU extension LIKE implementation suffers from the same + problem and uses the same solution. However, since the ICU extension + code does not include the SQLite file "limits.h", modifying + the default value therein does not affect the ICU extension. + The default value of SQLITE_MAX_LIKE_PATTERN_LENGTH used by + the ICU extension LIKE operator is 50000, defined in source + file "icu.c". + + 3.3 Collation Sequence Security Issue + + Internally, SQLite assumes that indices stored in database files + are sorted according to the collation sequence indicated by the + SQL schema. Changing the definition of a collation sequence after + an index has been built is therefore equivalent to database + corruption. The SQLite library is not very well tested under + these conditions, and may contain potential buffer overruns + or other programming errors that could be exploited by a malicious + programmer. + + If the ICU extension is used in an environment where potentially + malicious users may execute arbitrary SQL (i.e. gears), they + should be prevented from invoking the icu_load_collation() function, + possibly using the authorisation callback. diff --git a/ext/icu/icu.c b/ext/icu/icu.c new file mode 100644 index 0000000..ae28d70 --- /dev/null +++ b/ext/icu/icu.c @@ -0,0 +1,501 @@ +/* +** 2007 May 6 +** +** The author disclaims copyright to this source code. In place of +** a legal notice, here is a blessing: +** +** May you do good and not evil. +** May you find forgiveness for yourself and forgive others. +** May you share freely, never taking more than you give. +** +************************************************************************* +** $Id: icu.c,v 1.7 2007/12/13 21:54:11 drh Exp $ +** +** This file implements an integration between the ICU library +** ("International Components for Unicode", an open-source library +** for handling unicode data) and SQLite. The integration uses +** ICU to provide the following to SQLite: +** +** * An implementation of the SQL regexp() function (and hence REGEXP +** operator) using the ICU uregex_XX() APIs. +** +** * Implementations of the SQL scalar upper() and lower() functions +** for case mapping. +** +** * Integration of ICU and SQLite collation seqences. +** +** * An implementation of the LIKE operator that uses ICU to +** provide case-independent matching. +*/ + +#if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_ICU) + +/* Include ICU headers */ +#include <unicode/utypes.h> +#include <unicode/uregex.h> +#include <unicode/ustring.h> +#include <unicode/ucol.h> + +#include <assert.h> + +#ifndef SQLITE_CORE + #include "sqlite3ext.h" + SQLITE_EXTENSION_INIT1 +#else + #include "sqlite3.h" +#endif + +/* +** Maximum length (in bytes) of the pattern in a LIKE or GLOB +** operator. +*/ +#ifndef SQLITE_MAX_LIKE_PATTERN_LENGTH +# define SQLITE_MAX_LIKE_PATTERN_LENGTH 50000 +#endif + +/* +** Version of sqlite3_free() that is always a function, never a macro. +*/ +static void xFree(void *p){ + sqlite3_free(p); +} + +/* +** Compare two UTF-8 strings for equality where the first string is +** a "LIKE" expression. Return true (1) if they are the same and +** false (0) if they are different. +*/ +static int icuLikeCompare( + const uint8_t *zPattern, /* LIKE pattern */ + const uint8_t *zString, /* The UTF-8 string to compare against */ + const UChar32 uEsc /* The escape character */ +){ + static const int MATCH_ONE = (UChar32)'_'; + static const int MATCH_ALL = (UChar32)'%'; + + int iPattern = 0; /* Current byte index in zPattern */ + int iString = 0; /* Current byte index in zString */ + + int prevEscape = 0; /* True if the previous character was uEsc */ + + while( zPattern[iPattern]!=0 ){ + + /* Read (and consume) the next character from the input pattern. */ + UChar32 uPattern; + U8_NEXT_UNSAFE(zPattern, iPattern, uPattern); + assert(uPattern!=0); + + /* There are now 4 possibilities: + ** + ** 1. uPattern is an unescaped match-all character "%", + ** 2. uPattern is an unescaped match-one character "_", + ** 3. uPattern is an unescaped escape character, or + ** 4. uPattern is to be handled as an ordinary character + */ + if( !prevEscape && uPattern==MATCH_ALL ){ + /* Case 1. */ + uint8_t c; + + /* Skip any MATCH_ALL or MATCH_ONE characters that follow a + ** MATCH_ALL. For each MATCH_ONE, skip one character in the + ** test string. + */ + while( (c=zPattern[iPattern]) == MATCH_ALL || c == MATCH_ONE ){ + if( c==MATCH_ONE ){ + if( zString[iString]==0 ) return 0; + U8_FWD_1_UNSAFE(zString, iString); + } + iPattern++; + } + + if( zPattern[iPattern]==0 ) return 1; + + while( zString[iString] ){ + if( icuLikeCompare(&zPattern[iPattern], &zString[iString], uEsc) ){ + return 1; + } + U8_FWD_1_UNSAFE(zString, iString); + } + return 0; + + }else if( !prevEscape && uPattern==MATCH_ONE ){ + /* Case 2. */ + if( zString[iString]==0 ) return 0; + U8_FWD_1_UNSAFE(zString, iString); + + }else if( !prevEscape && uPattern==uEsc){ + /* Case 3. */ + prevEscape = 1; + + }else{ + /* Case 4. */ + UChar32 uString; + U8_NEXT_UNSAFE(zString, iString, uString); + uString = u_foldCase(uString, U_FOLD_CASE_DEFAULT); + uPattern = u_foldCase(uPattern, U_FOLD_CASE_DEFAULT); + if( uString!=uPattern ){ + return 0; + } + prevEscape = 0; + } + } + + return zString[iString]==0; +} + +/* +** Implementation of the like() SQL function. This function implements +** the build-in LIKE operator. The first argument to the function is the +** pattern and the second argument is the string. So, the SQL statements: +** +** A LIKE B +** +** is implemented as like(B, A). If there is an escape character E, +** +** A LIKE B ESCAPE E +** +** is mapped to like(B, A, E). +*/ +static void icuLikeFunc( + sqlite3_context *context, + int argc, + sqlite3_value **argv +){ + const unsigned char *zA = sqlite3_value_text(argv[0]); + const unsigned char *zB = sqlite3_value_text(argv[1]); + UChar32 uEsc = 0; + + /* Limit the length of the LIKE or GLOB pattern to avoid problems + ** of deep recursion and N*N behavior in patternCompare(). + */ + if( sqlite3_value_bytes(argv[0])>SQLITE_MAX_LIKE_PATTERN_LENGTH ){ + sqlite3_result_error(context, "LIKE or GLOB pattern too complex", -1); + return; + } + + + if( argc==3 ){ + /* The escape character string must consist of a single UTF-8 character. + ** Otherwise, return an error. + */ + int nE= sqlite3_value_bytes(argv[2]); + const unsigned char *zE = sqlite3_value_text(argv[2]); + int i = 0; + if( zE==0 ) return; + U8_NEXT(zE, i, nE, uEsc); + if( i!=nE){ + sqlite3_result_error(context, + "ESCAPE expression must be a single character", -1); + return; + } + } + + if( zA && zB ){ + sqlite3_result_int(context, icuLikeCompare(zA, zB, uEsc)); + } +} + +/* +** This function is called when an ICU function called from within +** the implementation of an SQL scalar function returns an error. +** +** The scalar function context passed as the first argument is +** loaded with an error message based on the following two args. +*/ +static void icuFunctionError( + sqlite3_context *pCtx, /* SQLite scalar function context */ + const char *zName, /* Name of ICU function that failed */ + UErrorCode e /* Error code returned by ICU function */ +){ + char zBuf[128]; + sqlite3_snprintf(128, zBuf, "ICU error: %s(): %s", zName, u_errorName(e)); + zBuf[127] = '\0'; + sqlite3_result_error(pCtx, zBuf, -1); +} + +/* +** Function to delete compiled regexp objects. Registered as +** a destructor function with sqlite3_set_auxdata(). +*/ +static void icuRegexpDelete(void *p){ + URegularExpression *pExpr = (URegularExpression *)p; + uregex_close(pExpr); +} + +/* +** Implementation of SQLite REGEXP operator. This scalar function takes +** two arguments. The first is a regular expression pattern to compile +** the second is a string to match against that pattern. If either +** argument is an SQL NULL, then NULL Is returned. Otherwise, the result +** is 1 if the string matches the pattern, or 0 otherwise. +** +** SQLite maps the regexp() function to the regexp() operator such +** that the following two are equivalent: +** +** zString REGEXP zPattern +** regexp(zPattern, zString) +** +** Uses the following ICU regexp APIs: +** +** uregex_open() +** uregex_matches() +** uregex_close() +*/ +static void icuRegexpFunc(sqlite3_context *p, int nArg, sqlite3_value **apArg){ + UErrorCode status = U_ZERO_ERROR; + URegularExpression *pExpr; + UBool res; + const UChar *zString = sqlite3_value_text16(apArg[1]); + + (void)nArg; /* Unused parameter */ + + /* If the left hand side of the regexp operator is NULL, + ** then the result is also NULL. + */ + if( !zString ){ + return; + } + + pExpr = sqlite3_get_auxdata(p, 0); + if( !pExpr ){ + const UChar *zPattern = sqlite3_value_text16(apArg[0]); + if( !zPattern ){ + return; + } + pExpr = uregex_open(zPattern, -1, 0, 0, &status); + + if( U_SUCCESS(status) ){ + sqlite3_set_auxdata(p, 0, pExpr, icuRegexpDelete); + }else{ + assert(!pExpr); + icuFunctionError(p, "uregex_open", status); + return; + } + } + + /* Configure the text that the regular expression operates on. */ + uregex_setText(pExpr, zString, -1, &status); + if( !U_SUCCESS(status) ){ + icuFunctionError(p, "uregex_setText", status); + return; + } + + /* Attempt the match */ + res = uregex_matches(pExpr, 0, &status); + if( !U_SUCCESS(status) ){ + icuFunctionError(p, "uregex_matches", status); + return; + } + + /* Set the text that the regular expression operates on to a NULL + ** pointer. This is not really necessary, but it is tidier than + ** leaving the regular expression object configured with an invalid + ** pointer after this function returns. + */ + uregex_setText(pExpr, 0, 0, &status); + + /* Return 1 or 0. */ + sqlite3_result_int(p, res ? 1 : 0); +} + +/* +** Implementations of scalar functions for case mapping - upper() and +** lower(). Function upper() converts its input to upper-case (ABC). +** Function lower() converts to lower-case (abc). +** +** ICU provides two types of case mapping, "general" case mapping and +** "language specific". Refer to ICU documentation for the differences +** between the two. +** +** To utilise "general" case mapping, the upper() or lower() scalar +** functions are invoked with one argument: +** +** upper('ABC') -> 'abc' +** lower('abc') -> 'ABC' +** +** To access ICU "language specific" case mapping, upper() or lower() +** should be invoked with two arguments. The second argument is the name +** of the locale to use. Passing an empty string ("") or SQL NULL value +** as the second argument is the same as invoking the 1 argument version +** of upper() or lower(). +** +** lower('I', 'en_us') -> 'i' +** lower('I', 'tr_tr') -> 'ı' (small dotless i) +** +** http://www.icu-project.org/userguide/posix.html#case_mappings +*/ +static void icuCaseFunc16(sqlite3_context *p, int nArg, sqlite3_value **apArg){ + const UChar *zInput; + UChar *zOutput; + int nInput; + int nOutput; + + UErrorCode status = U_ZERO_ERROR; + const char *zLocale = 0; + + assert(nArg==1 || nArg==2); + if( nArg==2 ){ + zLocale = (const char *)sqlite3_value_text(apArg[1]); + } + + zInput = sqlite3_value_text16(apArg[0]); + if( !zInput ){ + return; + } + nInput = sqlite3_value_bytes16(apArg[0]); + + nOutput = nInput * 2 + 2; + zOutput = sqlite3_malloc(nOutput); + if( !zOutput ){ + return; + } + + if( sqlite3_user_data(p) ){ + u_strToUpper(zOutput, nOutput/2, zInput, nInput/2, zLocale, &status); + }else{ + u_strToLower(zOutput, nOutput/2, zInput, nInput/2, zLocale, &status); + } + + if( !U_SUCCESS(status) ){ + icuFunctionError(p, "u_strToLower()/u_strToUpper", status); + return; + } + + sqlite3_result_text16(p, zOutput, -1, xFree); +} + +/* +** Collation sequence destructor function. The pCtx argument points to +** a UCollator structure previously allocated using ucol_open(). +*/ +static void icuCollationDel(void *pCtx){ + UCollator *p = (UCollator *)pCtx; + ucol_close(p); +} + +/* +** Collation sequence comparison function. The pCtx argument points to +** a UCollator structure previously allocated using ucol_open(). +*/ +static int icuCollationColl( + void *pCtx, + int nLeft, + const void *zLeft, + int nRight, + const void *zRight +){ + UCollationResult res; + UCollator *p = (UCollator *)pCtx; + res = ucol_strcoll(p, (UChar *)zLeft, nLeft/2, (UChar *)zRight, nRight/2); + switch( res ){ + case UCOL_LESS: return -1; + case UCOL_GREATER: return +1; + case UCOL_EQUAL: return 0; + } + assert(!"Unexpected return value from ucol_strcoll()"); + return 0; +} + +/* +** Implementation of the scalar function icu_load_collation(). +** +** This scalar function is used to add ICU collation based collation +** types to an SQLite database connection. It is intended to be called +** as follows: +** +** SELECT icu_load_collation(<locale>, <collation-name>); +** +** Where <locale> is a string containing an ICU locale identifier (i.e. +** "en_AU", "tr_TR" etc.) and <collation-name> is the name of the +** collation sequence to create. +*/ +static void icuLoadCollation( + sqlite3_context *p, + int nArg, + sqlite3_value **apArg +){ + sqlite3 *db = (sqlite3 *)sqlite3_user_data(p); + UErrorCode status = U_ZERO_ERROR; + const char *zLocale; /* Locale identifier - (eg. "jp_JP") */ + const char *zName; /* SQL Collation sequence name (eg. "japanese") */ + UCollator *pUCollator; /* ICU library collation object */ + int rc; /* Return code from sqlite3_create_collation_x() */ + + assert(nArg==2); + zLocale = (const char *)sqlite3_value_text(apArg[0]); + zName = (const char *)sqlite3_value_text(apArg[1]); + + if( !zLocale || !zName ){ + return; + } + + pUCollator = ucol_open(zLocale, &status); + if( !U_SUCCESS(status) ){ + icuFunctionError(p, "ucol_open", status); + return; + } + assert(p); + + rc = sqlite3_create_collation_v2(db, zName, SQLITE_UTF16, (void *)pUCollator, + icuCollationColl, icuCollationDel + ); + if( rc!=SQLITE_OK ){ + ucol_close(pUCollator); + sqlite3_result_error(p, "Error registering collation function", -1); + } +} + +/* +** Register the ICU extension functions with database db. +*/ +int sqlite3IcuInit(sqlite3 *db){ + struct IcuScalar { + const char *zName; /* Function name */ + int nArg; /* Number of arguments */ + int enc; /* Optimal text encoding */ + void *pContext; /* sqlite3_user_data() context */ + void (*xFunc)(sqlite3_context*,int,sqlite3_value**); + } scalars[] = { + {"regexp", 2, SQLITE_ANY, 0, icuRegexpFunc}, + + {"lower", 1, SQLITE_UTF16, 0, icuCaseFunc16}, + {"lower", 2, SQLITE_UTF16, 0, icuCaseFunc16}, + {"upper", 1, SQLITE_UTF16, (void*)1, icuCaseFunc16}, + {"upper", 2, SQLITE_UTF16, (void*)1, icuCaseFunc16}, + + {"lower", 1, SQLITE_UTF8, 0, icuCaseFunc16}, + {"lower", 2, SQLITE_UTF8, 0, icuCaseFunc16}, + {"upper", 1, SQLITE_UTF8, (void*)1, icuCaseFunc16}, + {"upper", 2, SQLITE_UTF8, (void*)1, icuCaseFunc16}, + + {"like", 2, SQLITE_UTF8, 0, icuLikeFunc}, + {"like", 3, SQLITE_UTF8, 0, icuLikeFunc}, + + {"icu_load_collation", 2, SQLITE_UTF8, (void*)db, icuLoadCollation}, + }; + + int rc = SQLITE_OK; + int i; + + for(i=0; rc==SQLITE_OK && i<(int)(sizeof(scalars)/sizeof(scalars[0])); i++){ + struct IcuScalar *p = &scalars[i]; + rc = sqlite3_create_function( + db, p->zName, p->nArg, p->enc, p->pContext, p->xFunc, 0, 0 + ); + } + + return rc; +} + +#if !SQLITE_CORE +int sqlite3_extension_init( + sqlite3 *db, + char **pzErrMsg, + const sqlite3_api_routines *pApi +){ + SQLITE_EXTENSION_INIT2(pApi) + return sqlite3IcuInit(db); +} +#endif + +#endif diff --git a/ext/icu/sqliteicu.h b/ext/icu/sqliteicu.h new file mode 100644 index 0000000..69b42f9 --- /dev/null +++ b/ext/icu/sqliteicu.h @@ -0,0 +1,27 @@ +/* +** 2008 May 26 +** +** The author disclaims copyright to this source code. In place of +** a legal notice, here is a blessing: +** +** May you do good and not evil. +** May you find forgiveness for yourself and forgive others. +** May you share freely, never taking more than you give. +** +****************************************************************************** +** +** This header file is used by programs that want to link against the +** ICU extension. All it does is declare the sqlite3IcuInit() interface. +*/ +#include "sqlite3.h" + +#ifdef __cplusplus +extern "C" { +#endif /* __cplusplus */ + +int sqlite3IcuInit(sqlite3 *db); + +#ifdef __cplusplus +} /* extern "C" */ +#endif /* __cplusplus */ + |