2 /* This work is part of OpenLDAP Software <http://www.openldap.org/>.
4 * Copyright 1999-2004 The OpenLDAP Foundation.
5 * Portions Copyright 1999 Dmitry Kovalev.
6 * Portions Copyright 2002 Pierangelo Mararati.
9 * Redistribution and use in source and binary forms, with or without
10 * modification, are permitted only as authorized by the OpenLDAP
13 * A copy of this license is available in the file LICENSE in the
14 * top-level directory of the distribution or, alternatively, at
15 * <http://www.OpenLDAP.org/license.html>.
18 * This work was initially developed by Dmitry Kovalev for inclusion
19 * by OpenLDAP Software. Additional significant contributors include
23 * Original copyright notice by Mark Adamson (applies to portions
24 * of code submitted as ITS#3432, and reworked by Pierangelo Masarati):
26 * The patch files are derived from OpenLDAP Software. All of the
27 * modifications to OpenLDAP Software represented in the following
28 * patches were developed by Mark Adamson (adamson@cmu.edu). These
29 * modifications are not subject to any license of Carnegie Mellon.
31 * The attached modifications to OpenLDAP Software are subject to the
34 * Copyright 2004 Mark Adamson
35 * Redistribution and use in source and binary forms, with or without
36 * modification, are permitted only as authorized by the OpenLDAP Public
40 * The following changes have been addressed:
43 * - re-styled code for better readability
44 * - upgraded backend API to reflect recent changes
45 * - LDAP schema is checked when loading SQL/LDAP mapping
46 * - AttributeDescription/ObjectClass pointers used for more efficient
48 * - bervals used where string length is required often
49 * - atomized write operations by committing at the end of each operation
50 * and defaulting connection closure to rollback
51 * - added LDAP access control to write operations
52 * - fully implemented modrdn (with rdn attrs change, deleteoldrdn,
53 * access check, parent/children check and more)
54 * - added parent access control, children control to delete operation
55 * - added structuralObjectClass operational attribute check and
56 * value return on search
57 * - added hasSubordinate operational attribute on demand
58 * - search limits are appropriately enforced
59 * - function backsql_strcat() has been made more efficient
60 * - concat function has been made configurable by means of a pattern
61 * - added config switches:
62 * - fail_if_no_mapping write operations fail if there is no mapping
63 * - has_ldapinfo_dn_ru overrides autodetect
64 * - concat_pattern a string containing two '?' is used
65 * (note that "?||?" should be more portable
66 * than builtin function "CONCAT(?,?)")
67 * - strcast_func cast of string constants in "SELECT DISTINCT
68 * statements (needed by PostgreSQL)
69 * - upper_needs_cast cast the argument of upper when required
70 * (basically when building dn substring queries)
71 * - added noop control
72 * - added values return filter control
73 * - hasSubordinate can be used in search filters (with limitations)
74 * - eliminated oc->name; use oc->oc->soc_cname instead
77 * - add security checks for SQL statements that can be injected (?)
78 * - re-test with previously supported RDBMs
79 * - replace dn_ru and so with normalized dn (no need for upper() and so
81 * - implement a backsql_normalize() function to replace the upper()
83 * - note that subtree deletion, subtree renaming and so could be easily
84 * implemented (rollback and consistency checks are available :)
85 * - implement "lastmod" and other operational stuff (ldap_entries table ?)
86 * - check how to allow multiple operations with one statement, to remove
87 * BACKSQL_REALLOC_STMT from modify.c (a more recent unixODBC lib?)
90 * Improvements submitted by (ITS#)
92 * 1. id_query.patch applied (with changes)
93 * 2. shortcut.patch applied (reworked)
94 * 3. create_hint.patch applied
95 * 4. count_query.patch rejected (conflicts with other features)
96 * 5. returncodes.patch applied (with sanity checks)
97 * 6. connpool.patch under evaluation
98 * 7. modoc.patch under evaluation
99 * 8. miscfixes.patch applied (reworked; FIXME: other
100 * operations may need to load the
101 * entire entry for ACL purposes)
103 * original description:
105 Changes that were made to the SQL backend.
107 The patches were made against 2.2.18 and can be applied individually,
108 but would best be applied in the numerical order of the file names.
109 A synopsis of each patch is given here:
112 1. Added an option to set SQL query for the "id_query" operation.
114 2. Added an option to the SQL backend called "use_subtree_shortcut".
115 When a search is performed, the SQL query includes a WHERE clause
116 which says the DN must be "LIKE %<searchbase>". The LIKE operation
117 can be slow in an RDBM. This shortcut option says that if the
118 searchbase of the LDAP search is the root DN of the SQL backend,
119 and thus all objects will match the LIKE operator, do not include
120 the "LIKE %<searchbase>" clause in the SQL query (it is replaced
121 instead by the always true "1=1" clause to keep the "AND"'s
122 working correctly). This option is off by default, and should be
123 turned on only if all objects to be found in the RDBM are under the
124 same root DN. Multiple backends working within the same RDBM table
125 space would encounter problems. LDAP searches whose searchbase are
126 not at the root DN will bypass this shortcut and employ the LIKE
129 3. Added a "create_hint" column to ldap_oc_mappings table. Allows
130 taking the value of an attr named in "create_hint" and passing it to
131 the create_proc procedure. This is necessary for when an objectClass's
132 table is partition indexed by some indexing column and thus the value
133 in that indexing column cannot change after the row is created. The
134 value for the indexed column is passed into the create_proc, which
135 uses it to fill in the indexed column as the new row is created.
137 4. When loading the values of an attribute, the count(*) of the number
138 of values is fetched first and memory is allocated for the array of
139 values and normalized values. The old system of loading the values one
140 by one and running realloc() on the array of values and normalized
141 values each time was badly fragmenting memory. The array of values and
142 normalized values would be side by side in memory, and realloc()'ing
143 them over and over would force them to leapfrog each other through all
144 of available memory. Attrs with a large number of values could not be
145 loaded without crashing the slapd daemon.
147 5. Added code to interpret the value returned by stored procedures
148 which have expect_return set. Returned value is interpreted as an LDAP
149 return code. This allows the distinction between the SQL failing to
150 execute and the SQL running to completion and returning an error code
151 which can indicate a policy violation.
153 6. Added RDBM connection pooling. Once an operation is finished the
154 connection to the RDBM is returned to a pool rather than closing.
155 Allows the next operation to skip the initialization and authentication
156 phases of contacting the RDBM. Also, if licensing with ODBC places
157 a limit on the number of connections, an LDAP thread can block waiting
158 for another thread to finish, so that no LDAP errors are returned
159 for having more LDAP connections than allowed RDBM connections. An
160 RDBM connection which receives an SQL error is marked as "tainted"
161 so that it will be closed rather than returned to the pool.
162 Also, RDBM connections must be bound to a given LDAP connection AND
163 operation number, and NOT just the connection number. Asynchronous
164 LDAP clients can have multiple simultaneous LDAP operations which
165 should not share the same RDBM connection. A given LDAP operation can
166 even make multiple SQL operations (e.g. a BIND operation which
167 requires SASL to perform an LDAP search to convert the SASL ID to an
168 LDAP DN), so each RDBM connection now has a refcount that must reach
169 zero before the connection is returned to the free pool.
171 7. Added ability to change the objectClass of an object. Required
172 considerable work to copy all attributes out of old object and into
173 new object. Does a schema check before proceeding. Creates a new
174 object, fills it in, deletes the old object, then changes the
175 oc_map_id and keyval of the entry in the "ldap_entries" table.
177 8. Generic fixes. Includes initializing pointers before they
178 get used in error branch cases, pointer checks before dereferencing,
179 resetting a return code to success after a COMPARE op, sealing
180 memory leaks, and in search.c, changing some of the "1=1" tests to
181 "2=2", "3=3", etc so that when reading slapd trace output, the
182 location in the source code where the x=x test was added to the SQL
183 can be easily distinguished.
186 #ifndef __BACKSQL_H__
187 #define __BACKSQL_H__
189 #include "sql-types.h"
192 * Better use the standard length of 8192 (as of slap.h)?
194 * NOTE: must be consistent with definition in ldap_entries table
196 /* #define BACKSQL_MAX_DN_LEN SLAP_LDAPDN_MAXLEN */
197 #define BACKSQL_MAX_DN_LEN 255
200 * define to enable very extensive trace logging (debug only)
205 * define to enable varchars as unique keys in user tables
207 * by default integers are used (and recommended)
208 * for performances. Integers are used anyway in back-sql
211 #undef BACKSQL_ARBITRARY_KEY
214 * define to enable experimental support for syncporv overlay
217 #define BACKSQL_SYNCPROV
218 #endif /* LDAP_DEVEL */
221 * define to the appropriate aliasing string
223 * some RDBMSes tolerate (or require) that " AS " is not used
224 * when aliasing tables/columns
226 #define BACKSQL_ALIASING "AS "
227 /* #define BACKSQL_ALIASING "" */
230 * define to the appropriate quoting char
232 * some RDBMSes tolerate/require that the aliases be enclosed
233 * in quotes. This is especially true for those that do not
234 * allow keywords used as aliases.
236 /* #define BACKSQL_ALIASING_QUOTE '"' */
237 /* #define BACKSQL_ALIASING_QUOTE '\'' */
242 * a simple mechanism to allow DN mucking between the LDAP
243 * and the stored string representation.
245 typedef struct backsql_api {
247 int (*ba_dn2odbc)( Operation *op, SlapReply *rs, struct berval *dn );
248 int (*ba_odbc2dn)( Operation *op, SlapReply *rs, struct berval *dn );
249 struct backsql_api *ba_next;
255 typedef struct backsql_entryID {
256 /* #define BACKSQL_ARBITRARY_KEY to allow a non-numeric key.
257 * It is required by some special applications that use
258 * strings as keys for the main table.
259 * In this case, #define BACKSQL_MAX_KEY_LEN consistently
260 * with the key size definition */
261 #ifdef BACKSQL_ARBITRARY_KEY
262 struct berval eid_id;
263 struct berval eid_keyval;
264 #define BACKSQL_MAX_KEY_LEN 64
265 #else /* ! BACKSQL_ARBITRARY_KEY */
266 /* The original numeric key is maintained as default. */
267 unsigned long eid_id;
268 unsigned long eid_keyval;
269 #endif /* ! BACKSQL_ARBITRARY_KEY */
271 unsigned long eid_oc_id;
272 struct berval eid_dn;
273 struct berval eid_ndn;
274 struct backsql_entryID *eid_next;
277 #ifdef BACKSQL_ARBITRARY_KEY
278 #define BACKSQL_ENTRYID_INIT { BER_BVNULL, BER_BVNULL, 0, BER_BVNULL, BER_BVNULL, NULL }
279 #else /* ! BACKSQL_ARBITRARY_KEY */
280 #define BACKSQL_ENTRYID_INIT { 0, 0, 0, BER_BVNULL, BER_BVNULL, NULL }
281 #endif /* BACKSQL_ARBITRARY_KEY */
284 * "structural" objectClass mapping structure
286 typedef struct backsql_oc_map_rec {
288 * Structure of corresponding LDAP objectClass definition
291 #define BACKSQL_OC_NAME(ocmap) ((ocmap)->bom_oc->soc_cname.bv_val)
293 struct berval bom_keytbl;
294 struct berval bom_keycol;
295 /* expected to return keyval of newly created entry */
296 char *bom_create_proc;
297 /* in case create_proc does not return the keyval of the newly
299 char *bom_create_keyval;
300 /* supposed to expect keyval as parameter and delete
301 * all the attributes as well */
302 char *bom_delete_proc;
303 /* flags whether delete_proc is a function (whether back-sql
304 * should bind first parameter as output for return code) */
305 int bom_expect_return;
306 unsigned long bom_id;
308 AttributeDescription *bom_create_hint;
309 } backsql_oc_map_rec;
312 * attributeType mapping structure
314 typedef struct backsql_at_map_rec {
315 /* Description of corresponding LDAP attribute type */
316 AttributeDescription *bam_ad;
317 /* ObjectClass if bam_ad is objectClass */
320 struct berval bam_from_tbls;
321 struct berval bam_join_where;
322 struct berval bam_sel_expr;
324 /* TimesTen, or, if a uppercase function is defined,
325 * an uppercased version of bam_sel_expr */
326 struct berval bam_sel_expr_u;
328 /* supposed to expect 2 binded values: entry keyval
329 * and attr. value to add, like "add_name(?,?,?)" */
331 /* supposed to expect 2 binded values: entry keyval
332 * and attr. value to delete */
333 char *bam_delete_proc;
334 /* for optimization purposes attribute load query
335 * is preconstructed from parts on schemamap load time */
337 /* following flags are bitmasks (first bit used for add_proc,
338 * second - for delete_proc) */
339 /* order of parameters for procedures above;
340 * 1 means "data then keyval", 0 means "keyval then data" */
342 /* flags whether one or more of procedures is a function
343 * (whether back-sql should bind first parameter as output
344 * for return code) */
345 int bam_expect_return;
347 /* next mapping for attribute */
348 struct backsql_at_map_rec *bam_next;
349 } backsql_at_map_rec;
351 #define BACKSQL_AT_MAP_REC_INIT { NULL, NULL, BER_BVC(""), BER_BVC(""), BER_BVNULL, BER_BVNULL, NULL, NULL, NULL, 0, 0, NULL }
353 /* define to uppercase filters only if the matching rule requires it
354 * (currently broken) */
355 /* #define BACKSQL_UPPERCASE_FILTER */
357 #define BACKSQL_AT_CANUPPERCASE(at) ((at)->bam_sel_expr_u.bv_val)
359 /* defines to support bitmasks above */
360 #define BACKSQL_ADD 0x1
361 #define BACKSQL_DEL 0x2
363 #define BACKSQL_IS_ADD(x) ( BACKSQL_ADD & (x) )
364 #define BACKSQL_IS_DEL(x) ( BACKSQL_DEL & (x) )
366 #define BACKSQL_NCMP(v1,v2) ber_bvcmp((v1),(v2))
368 #define BACKSQL_CONCAT
370 * berbuf structure: a berval with a buffer size associated
372 typedef struct berbuf {
373 struct berval bb_val;
377 #define BB_NULL { { 0, NULL }, 0 }
379 typedef struct backsql_srch_info {
384 #define BSQL_SF_NONE 0x0000U
385 #define BSQL_SF_ALL_USER 0x0001U
386 #define BSQL_SF_ALL_OPER 0x0002U
387 #define BSQL_SF_ALL_ATTRS (BSQL_SF_ALL_USER|BSQL_SF_ALL_OPER)
388 #define BSQL_SF_FILTER_HASSUBORDINATE 0x0010U
389 #define BSQL_SF_FILTER_ENTRYUUID 0x0020U
390 #define BSQL_SF_FILTER_ENTRYCSN 0x0040U
391 #define BSQL_SF_RETURN_ENTRYUUID (BSQL_SF_FILTER_ENTRYUUID << 8)
393 struct berval *bsi_base_ndn;
394 int bsi_use_subtree_shortcut;
395 backsql_entryID bsi_base_id;
397 /* BACKSQL_SCOPE_BASE_LIKE can be set by API in ors_scope
398 * whenever the search base DN contains chars that cannot
399 * be mapped into the charset used in the RDBMS; so they're
400 * turned into '%' and an approximate ('LIKE') condition
402 #define BACKSQL_SCOPE_BASE_LIKE ( LDAP_SCOPE_BASE | 0x1000 )
408 backsql_entryID *bsi_id_list,
411 int bsi_n_candidates;
415 backsql_oc_map_rec *bsi_oc;
416 struct berbuf bsi_sel,
420 ObjectClass *bsi_filter_oc;
422 AttributeName *bsi_attrs;
428 * Backend private data structure
438 * SQL condition for subtree searches differs in syntax:
439 * "LIKE CONCAT('%',?)" or "LIKE '%'+?" or "LIKE '%'||?"
442 struct berval sql_subtree_cond;
443 struct berval sql_children_cond;
446 char *sql_insentry_query,
448 *sql_delobjclasses_query,
449 *sql_delreferrals_query;
451 char *sql_has_children_query;
453 MatchingRule *sql_caseIgnoreMatch;
454 MatchingRule *sql_telephoneNumberMatch;
456 struct berval sql_upper_func;
457 struct berval sql_upper_func_open;
458 struct berval sql_upper_func_close;
459 BerVarray sql_concat_func;
461 struct berval sql_strcast_func;
463 unsigned int sql_flags;
464 #define BSQLF_SCHEMA_LOADED 0x0001
465 #define BSQLF_UPPER_NEEDS_CAST 0x0002
466 #define BSQLF_CREATE_NEEDS_SELECT 0x0004
467 #define BSQLF_FAIL_IF_NO_MAPPING 0x0008
468 #define BSQLF_HAS_LDAPINFO_DN_RU 0x0010
469 #define BSQLF_DONTCHECK_LDAPINFO_DN_RU 0x0020
470 #define BSQLF_USE_REVERSE_DN 0x0040
471 #define BSQLF_ALLOW_ORPHANS 0x0080
472 #define BSQLF_USE_SUBTREE_SHORTCUT 0x0100
474 #define BACKSQL_SCHEMA_LOADED(si) \
475 ((si)->sql_flags & BSQLF_SCHEMA_LOADED)
476 #define BACKSQL_UPPER_NEEDS_CAST(si) \
477 ((si)->sql_flags & BSQLF_UPPER_NEEDS_CAST)
478 #define BACKSQL_CREATE_NEEDS_SELECT(si) \
479 ((si)->sql_flags & BSQLF_CREATE_NEEDS_SELECT)
480 #define BACKSQL_FAIL_IF_NO_MAPPING(si) \
481 ((si)->sql_flags & BSQLF_FAIL_IF_NO_MAPPING)
482 #define BACKSQL_HAS_LDAPINFO_DN_RU(si) \
483 ((si)->sql_flags & BSQLF_HAS_LDAPINFO_DN_RU)
484 #define BACKSQL_DONTCHECK_LDAPINFO_DN_RU(si) \
485 ((si)->sql_flags & BSQLF_DONTCHECK_LDAPINFO_DN_RU)
486 #define BACKSQL_USE_REVERSE_DN(si) \
487 ((si)->sql_flags & BSQLF_USE_REVERSE_DN)
488 #define BACKSQL_CANUPPERCASE(si) \
489 (!BER_BVISNULL( &(si)->sql_upper_func ))
490 #define BACKSQL_ALLOW_ORPHANS(si) \
491 ((si)->sql_flags & BSQLF_ALLOW_ORPHANS)
492 #define BACKSQL_USE_SUBTREE_SHORTCUT(si) \
493 ((si)->sql_flags & BSQLF_USE_SUBTREE_SHORTCUT)
495 Entry *sql_baseObject;
496 #ifdef BACKSQL_ARBITRARY_KEY
497 #define BACKSQL_BASEOBJECT_IDSTR "baseObject"
498 #define BACKSQL_BASEOBJECT_KEYVAL BACKSQL_BASEOBJECT_IDSTR
499 #define BACKSQL_IS_BASEOBJECT_ID(id) (bvmatch((id), &backsql_baseObject_bv))
500 #else /* ! BACKSQL_ARBITRARY_KEY */
501 #define BACKSQL_BASEOBJECT_ID 0
502 #define BACKSQL_BASEOBJECT_IDSTR "0"
503 #define BACKSQL_BASEOBJECT_KEYVAL 0
504 #define BACKSQL_IS_BASEOBJECT_ID(id) (*(id) == BACKSQL_BASEOBJECT_ID)
505 #endif /* ! BACKSQL_ARBITRARY_KEY */
506 #define BACKSQL_BASEOBJECT_OC 0
508 Avlnode *sql_db_conns;
509 Avlnode *sql_oc_by_oc;
510 Avlnode *sql_oc_by_id;
511 ldap_pvt_thread_mutex_t sql_dbconn_mutex;
512 ldap_pvt_thread_mutex_t sql_schema_mutex;
515 backsql_api *sql_api;
518 #define BACKSQL_SUCCESS( rc ) \
519 ( (rc) == SQL_SUCCESS || (rc) == SQL_SUCCESS_WITH_INFO )
521 #define BACKSQL_AVL_STOP 0
522 #define BACKSQL_AVL_CONTINUE 1
524 /* see ldap.h for the meaning of the macros and of the values */
525 #define BACKSQL_LEGAL_ERROR( rc ) \
526 ( LDAP_RANGE( (rc), 0x00, 0x0e ) \
527 || LDAP_ATTR_ERROR( (rc) ) \
528 || LDAP_NAME_ERROR( (rc) ) \
529 || LDAP_SECURITY_ERROR( (rc) ) \
530 || LDAP_SERVICE_ERROR( (rc) ) \
531 || LDAP_UPDATE_ERROR( (rc) ) )
532 #define BACKSQL_SANITIZE_ERROR( rc ) \
533 ( BACKSQL_LEGAL_ERROR( (rc) ) ? (rc) : LDAP_OTHER )
535 #endif /* __BACKSQL_H__ */