1 /* $NetBSD: sql-wrap.c,v 1.2 2020/08/11 13:15:42 christos Exp $ */ 2 3 /* $OpenLDAP$ */ 4 /* This work is part of OpenLDAP Software <http://www.openldap.org/>. 5 * 6 * Copyright 1999-2020 The OpenLDAP Foundation. 7 * Portions Copyright 1999 Dmitry Kovalev. 8 * Portions Copyright 2002 Pierangelo Masarati. 9 * Portions Copyright 2004 Mark Adamson. 10 * All rights reserved. 11 * 12 * Redistribution and use in source and binary forms, with or without 13 * modification, are permitted only as authorized by the OpenLDAP 14 * Public License. 15 * 16 * A copy of this license is available in the file LICENSE in the 17 * top-level directory of the distribution or, alternatively, at 18 * <http://www.OpenLDAP.org/license.html>. 19 */ 20 /* ACKNOWLEDGEMENTS: 21 * This work was initially developed by Dmitry Kovalev for inclusion 22 * by OpenLDAP Software. Additional significant contributors include 23 * Pierangelo Masarati and Mark Adamson. 24 */ 25 26 #include <sys/cdefs.h> 27 __RCSID("$NetBSD: sql-wrap.c,v 1.2 2020/08/11 13:15:42 christos Exp $"); 28 29 #include "portable.h" 30 31 #include <stdio.h> 32 #include "ac/string.h" 33 #include <sys/types.h> 34 35 #include "slap.h" 36 #include "proto-sql.h" 37 38 #define MAX_ATTR_LEN 16384 39 40 void 41 backsql_PrintErrors( SQLHENV henv, SQLHDBC hdbc, SQLHSTMT sth, int rc ) 42 { 43 SQLCHAR msg[SQL_MAX_MESSAGE_LENGTH]; /* msg. buffer */ 44 SQLCHAR state[SQL_SQLSTATE_SIZE]; /* statement buf. */ 45 SDWORD iSqlCode; /* return code */ 46 SWORD len = SQL_MAX_MESSAGE_LENGTH - 1; /* return length */ 47 48 Debug( LDAP_DEBUG_TRACE, "Return code: %d\n", rc, 0, 0 ); 49 50 for ( ; rc = SQLError( henv, hdbc, sth, state, &iSqlCode, msg, 51 SQL_MAX_MESSAGE_LENGTH - 1, &len ), BACKSQL_SUCCESS( rc ); ) 52 { 53 Debug( LDAP_DEBUG_TRACE, 54 " nativeErrCode=%d SQLengineState=%s msg=\"%s\"\n", 55 (int)iSqlCode, state, msg ); 56 } 57 } 58 59 RETCODE 60 backsql_Prepare( SQLHDBC dbh, SQLHSTMT *sth, const char *query, int timeout ) 61 { 62 RETCODE rc; 63 64 rc = SQLAllocStmt( dbh, sth ); 65 if ( rc != SQL_SUCCESS ) { 66 return rc; 67 } 68 69 #ifdef BACKSQL_TRACE 70 Debug( LDAP_DEBUG_TRACE, "==>backsql_Prepare()\n", 0, 0, 0 ); 71 #endif /* BACKSQL_TRACE */ 72 73 #ifdef BACKSQL_MSSQL_WORKAROUND 74 { 75 char drv_name[ 30 ]; 76 SWORD len; 77 78 SQLGetInfo( dbh, SQL_DRIVER_NAME, drv_name, sizeof( drv_name ), &len ); 79 80 #ifdef BACKSQL_TRACE 81 Debug( LDAP_DEBUG_TRACE, "backsql_Prepare(): driver name=\"%s\"\n", 82 drv_name, 0, 0 ); 83 #endif /* BACKSQL_TRACE */ 84 85 ldap_pvt_str2upper( drv_name ); 86 if ( !strncmp( drv_name, "SQLSRV32.DLL", STRLENOF( "SQLSRV32.DLL" ) ) ) { 87 /* 88 * stupid default result set in MS SQL Server 89 * does not support multiple active statements 90 * on the same connection -- so we are trying 91 * to make it not to use default result set... 92 */ 93 Debug( LDAP_DEBUG_TRACE, "_SQLprepare(): " 94 "enabling MS SQL Server default result " 95 "set workaround\n", 0, 0, 0 ); 96 rc = SQLSetStmtOption( *sth, SQL_CONCURRENCY, 97 SQL_CONCUR_ROWVER ); 98 if ( rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO ) { 99 Debug( LDAP_DEBUG_TRACE, "backsql_Prepare(): " 100 "SQLSetStmtOption(SQL_CONCURRENCY," 101 "SQL_CONCUR_ROWVER) failed:\n", 102 0, 0, 0 ); 103 backsql_PrintErrors( SQL_NULL_HENV, dbh, *sth, rc ); 104 SQLFreeStmt( *sth, SQL_DROP ); 105 return rc; 106 } 107 } 108 } 109 #endif /* BACKSQL_MSSQL_WORKAROUND */ 110 111 if ( timeout > 0 ) { 112 Debug( LDAP_DEBUG_TRACE, "_SQLprepare(): " 113 "setting query timeout to %d sec.\n", 114 timeout, 0, 0 ); 115 rc = SQLSetStmtOption( *sth, SQL_QUERY_TIMEOUT, timeout ); 116 if ( rc != SQL_SUCCESS ) { 117 backsql_PrintErrors( SQL_NULL_HENV, dbh, *sth, rc ); 118 SQLFreeStmt( *sth, SQL_DROP ); 119 return rc; 120 } 121 } 122 123 #ifdef BACKSQL_TRACE 124 Debug( LDAP_DEBUG_TRACE, "<==backsql_Prepare() calling SQLPrepare()\n", 125 0, 0, 0 ); 126 #endif /* BACKSQL_TRACE */ 127 128 return SQLPrepare( *sth, (SQLCHAR *)query, SQL_NTS ); 129 } 130 131 RETCODE 132 backsql_BindRowAsStrings_x( SQLHSTMT sth, BACKSQL_ROW_NTS *row, void *ctx ) 133 { 134 RETCODE rc; 135 136 if ( row == NULL ) { 137 return SQL_ERROR; 138 } 139 140 #ifdef BACKSQL_TRACE 141 Debug( LDAP_DEBUG_TRACE, "==> backsql_BindRowAsStrings()\n", 0, 0, 0 ); 142 #endif /* BACKSQL_TRACE */ 143 144 rc = SQLNumResultCols( sth, &row->ncols ); 145 if ( rc != SQL_SUCCESS ) { 146 #ifdef BACKSQL_TRACE 147 Debug( LDAP_DEBUG_TRACE, "backsql_BindRowAsStrings(): " 148 "SQLNumResultCols() failed:\n", 0, 0, 0 ); 149 #endif /* BACKSQL_TRACE */ 150 151 backsql_PrintErrors( SQL_NULL_HENV, SQL_NULL_HDBC, sth, rc ); 152 153 } else { 154 SQLCHAR colname[ 64 ]; 155 SQLSMALLINT name_len, col_type, col_scale, col_null; 156 SQLLEN col_prec; 157 int i; 158 159 #ifdef BACKSQL_TRACE 160 Debug( LDAP_DEBUG_TRACE, "backsql_BindRowAsStrings: " 161 "ncols=%d\n", (int)row->ncols, 0, 0 ); 162 #endif /* BACKSQL_TRACE */ 163 164 row->col_names = (BerVarray)ber_memcalloc_x( row->ncols + 1, 165 sizeof( struct berval ), ctx ); 166 if ( row->col_names == NULL ) { 167 goto nomem; 168 } 169 170 row->col_prec = (UDWORD *)ber_memcalloc_x( row->ncols, 171 sizeof( UDWORD ), ctx ); 172 if ( row->col_prec == NULL ) { 173 goto nomem; 174 } 175 176 row->col_type = (SQLSMALLINT *)ber_memcalloc_x( row->ncols, 177 sizeof( SQLSMALLINT ), ctx ); 178 if ( row->col_type == NULL ) { 179 goto nomem; 180 } 181 182 row->cols = (char **)ber_memcalloc_x( row->ncols + 1, 183 sizeof( char * ), ctx ); 184 if ( row->cols == NULL ) { 185 goto nomem; 186 } 187 188 row->value_len = (SQLLEN *)ber_memcalloc_x( row->ncols, 189 sizeof( SQLLEN ), ctx ); 190 if ( row->value_len == NULL ) { 191 goto nomem; 192 } 193 194 if ( 0 ) { 195 nomem: 196 ber_memfree_x( row->col_names, ctx ); 197 row->col_names = NULL; 198 ber_memfree_x( row->col_prec, ctx ); 199 row->col_prec = NULL; 200 ber_memfree_x( row->col_type, ctx ); 201 row->col_type = NULL; 202 ber_memfree_x( row->cols, ctx ); 203 row->cols = NULL; 204 ber_memfree_x( row->value_len, ctx ); 205 row->value_len = NULL; 206 207 Debug( LDAP_DEBUG_ANY, "backsql_BindRowAsStrings: " 208 "out of memory\n", 0, 0, 0 ); 209 210 return LDAP_NO_MEMORY; 211 } 212 213 for ( i = 0; i < row->ncols; i++ ) { 214 SQLSMALLINT TargetType; 215 216 rc = SQLDescribeCol( sth, (SQLSMALLINT)(i + 1), &colname[ 0 ], 217 (SQLUINTEGER)( sizeof( colname ) - 1 ), 218 &name_len, &col_type, 219 &col_prec, &col_scale, &col_null ); 220 /* FIXME: test rc? */ 221 222 ber_str2bv_x( (char *)colname, 0, 1, 223 &row->col_names[ i ], ctx ); 224 #ifdef BACKSQL_TRACE 225 Debug( LDAP_DEBUG_TRACE, "backsql_BindRowAsStrings: " 226 "col_name=%s, col_prec[%d]=%d\n", 227 colname, (int)(i + 1), (int)col_prec ); 228 #endif /* BACKSQL_TRACE */ 229 if ( col_type != SQL_CHAR && col_type != SQL_VARCHAR ) 230 { 231 col_prec = MAX_ATTR_LEN; 232 } 233 234 row->cols[ i ] = (char *)ber_memcalloc_x( col_prec + 1, 235 sizeof( char ), ctx ); 236 row->col_prec[ i ] = col_prec; 237 row->col_type[ i ] = col_type; 238 239 /* 240 * ITS#3386, ITS#3113 - 20070308 241 * Note: there are many differences between various DPMS and ODBC 242 * Systems; some support SQL_C_BLOB, SQL_C_BLOB_LOCATOR. YMMV: 243 * This has only been tested on Linux/MySQL/UnixODBC 244 * For BINARY-type Fields (BLOB, etc), read the data as BINARY 245 */ 246 if ( BACKSQL_IS_BINARY( col_type ) ) { 247 #ifdef BACKSQL_TRACE 248 Debug( LDAP_DEBUG_TRACE, "backsql_BindRowAsStrings: " 249 "col_name=%s, col_type[%d]=%d: reading binary data\n", 250 colname, (int)(i + 1), (int)col_type); 251 #endif /* BACKSQL_TRACE */ 252 TargetType = SQL_C_BINARY; 253 254 } else { 255 /* Otherwise read it as Character data */ 256 #ifdef BACKSQL_TRACE 257 Debug( LDAP_DEBUG_TRACE, "backsql_BindRowAsStrings: " 258 "col_name=%s, col_type[%d]=%d: reading character data\n", 259 colname, (int)(i + 1), (int)col_type); 260 #endif /* BACKSQL_TRACE */ 261 TargetType = SQL_C_CHAR; 262 } 263 264 rc = SQLBindCol( sth, (SQLUSMALLINT)(i + 1), 265 TargetType, 266 (SQLPOINTER)row->cols[ i ], 267 col_prec + 1, 268 &row->value_len[ i ] ); 269 270 /* FIXME: test rc? */ 271 } 272 273 BER_BVZERO( &row->col_names[ i ] ); 274 row->cols[ i ] = NULL; 275 } 276 277 #ifdef BACKSQL_TRACE 278 Debug( LDAP_DEBUG_TRACE, "<== backsql_BindRowAsStrings()\n", 0, 0, 0 ); 279 #endif /* BACKSQL_TRACE */ 280 281 return rc; 282 } 283 284 RETCODE 285 backsql_BindRowAsStrings( SQLHSTMT sth, BACKSQL_ROW_NTS *row ) 286 { 287 return backsql_BindRowAsStrings_x( sth, row, NULL ); 288 } 289 290 RETCODE 291 backsql_FreeRow_x( BACKSQL_ROW_NTS *row, void *ctx ) 292 { 293 if ( row->cols == NULL ) { 294 return SQL_ERROR; 295 } 296 297 ber_bvarray_free_x( row->col_names, ctx ); 298 ber_memfree_x( row->col_prec, ctx ); 299 ber_memfree_x( row->col_type, ctx ); 300 ber_memvfree_x( (void **)row->cols, ctx ); 301 ber_memfree_x( row->value_len, ctx ); 302 303 return SQL_SUCCESS; 304 } 305 306 307 RETCODE 308 backsql_FreeRow( BACKSQL_ROW_NTS *row ) 309 { 310 return backsql_FreeRow_x( row, NULL ); 311 } 312 313 static void 314 backsql_close_db_handle( SQLHDBC dbh ) 315 { 316 if ( dbh == SQL_NULL_HDBC ) { 317 return; 318 } 319 320 Debug( LDAP_DEBUG_TRACE, "==>backsql_close_db_handle(%p)\n", 321 (void *)dbh, 0, 0 ); 322 323 /* 324 * Default transact is SQL_ROLLBACK; commit is required only 325 * by write operations, and it is explicitly performed after 326 * each atomic operation succeeds. 327 */ 328 329 /* TimesTen */ 330 SQLTransact( SQL_NULL_HENV, dbh, SQL_ROLLBACK ); 331 SQLDisconnect( dbh ); 332 SQLFreeConnect( dbh ); 333 334 Debug( LDAP_DEBUG_TRACE, "<==backsql_close_db_handle(%p)\n", 335 (void *)dbh, 0, 0 ); 336 } 337 338 int 339 backsql_conn_destroy( 340 backsql_info *bi ) 341 { 342 return 0; 343 } 344 345 int 346 backsql_init_db_env( backsql_info *bi ) 347 { 348 RETCODE rc; 349 int ret = SQL_SUCCESS; 350 351 Debug( LDAP_DEBUG_TRACE, "==>backsql_init_db_env()\n", 0, 0, 0 ); 352 353 rc = SQLAllocEnv( &bi->sql_db_env ); 354 if ( rc != SQL_SUCCESS ) { 355 Debug( LDAP_DEBUG_TRACE, "init_db_env: SQLAllocEnv failed:\n", 356 0, 0, 0 ); 357 backsql_PrintErrors( SQL_NULL_HENV, SQL_NULL_HDBC, 358 SQL_NULL_HENV, rc ); 359 ret = SQL_ERROR; 360 } 361 362 Debug( LDAP_DEBUG_TRACE, "<==backsql_init_db_env()=%d\n", ret, 0, 0 ); 363 364 return ret; 365 } 366 367 int 368 backsql_free_db_env( backsql_info *bi ) 369 { 370 Debug( LDAP_DEBUG_TRACE, "==>backsql_free_db_env()\n", 0, 0, 0 ); 371 372 (void)SQLFreeEnv( bi->sql_db_env ); 373 bi->sql_db_env = SQL_NULL_HENV; 374 375 /* 376 * stop, if frontend waits for all threads to shutdown 377 * before calling this -- then what are we going to delete?? 378 * everything is already deleted... 379 */ 380 Debug( LDAP_DEBUG_TRACE, "<==backsql_free_db_env()\n", 0, 0, 0 ); 381 382 return SQL_SUCCESS; 383 } 384 385 static int 386 backsql_open_db_handle( 387 backsql_info *bi, 388 SQLHDBC *dbhp ) 389 { 390 /* TimesTen */ 391 char DBMSName[ 32 ]; 392 int rc; 393 394 assert( dbhp != NULL ); 395 *dbhp = SQL_NULL_HDBC; 396 397 Debug( LDAP_DEBUG_TRACE, "==>backsql_open_db_handle()\n", 398 0, 0, 0 ); 399 400 rc = SQLAllocConnect( bi->sql_db_env, dbhp ); 401 if ( !BACKSQL_SUCCESS( rc ) ) { 402 Debug( LDAP_DEBUG_TRACE, "backsql_open_db_handle(): " 403 "SQLAllocConnect() failed:\n", 404 0, 0, 0 ); 405 backsql_PrintErrors( bi->sql_db_env, SQL_NULL_HDBC, 406 SQL_NULL_HENV, rc ); 407 return LDAP_UNAVAILABLE; 408 } 409 410 rc = SQLConnect( *dbhp, 411 (SQLCHAR*)bi->sql_dbname, SQL_NTS, 412 (SQLCHAR*)bi->sql_dbuser, SQL_NTS, 413 (SQLCHAR*)bi->sql_dbpasswd, SQL_NTS ); 414 if ( rc != SQL_SUCCESS ) { 415 Debug( LDAP_DEBUG_TRACE, "backsql_open_db_handle(): " 416 "SQLConnect() to database \"%s\" %s.\n", 417 bi->sql_dbname, 418 rc == SQL_SUCCESS_WITH_INFO ? 419 "succeeded with info" : "failed", 420 0 ); 421 backsql_PrintErrors( bi->sql_db_env, *dbhp, SQL_NULL_HENV, rc ); 422 if ( rc != SQL_SUCCESS_WITH_INFO ) { 423 SQLFreeConnect( *dbhp ); 424 return LDAP_UNAVAILABLE; 425 } 426 } 427 428 /* 429 * TimesTen : Turn off autocommit. We must explicitly 430 * commit any transactions. 431 */ 432 SQLSetConnectOption( *dbhp, SQL_AUTOCOMMIT, 433 BACKSQL_AUTOCOMMIT_ON( bi ) ? SQL_AUTOCOMMIT_ON : SQL_AUTOCOMMIT_OFF ); 434 435 /* 436 * See if this connection is to TimesTen. If it is, 437 * remember that fact for later use. 438 */ 439 /* Assume until proven otherwise */ 440 bi->sql_flags &= ~BSQLF_USE_REVERSE_DN; 441 DBMSName[ 0 ] = '\0'; 442 rc = SQLGetInfo( *dbhp, SQL_DBMS_NAME, (PTR)&DBMSName, 443 sizeof( DBMSName ), NULL ); 444 if ( rc == SQL_SUCCESS ) { 445 if ( strcmp( DBMSName, "TimesTen" ) == 0 || 446 strcmp( DBMSName, "Front-Tier" ) == 0 ) 447 { 448 Debug( LDAP_DEBUG_TRACE, "backsql_open_db_handle(): " 449 "TimesTen database!\n", 450 0, 0, 0 ); 451 bi->sql_flags |= BSQLF_USE_REVERSE_DN; 452 } 453 454 } else { 455 Debug( LDAP_DEBUG_TRACE, "backsql_open_db_handle(): " 456 "SQLGetInfo() failed.\n", 457 0, 0, 0 ); 458 backsql_PrintErrors( bi->sql_db_env, *dbhp, SQL_NULL_HENV, rc ); 459 SQLDisconnect( *dbhp ); 460 SQLFreeConnect( *dbhp ); 461 return LDAP_UNAVAILABLE; 462 } 463 /* end TimesTen */ 464 465 Debug( LDAP_DEBUG_TRACE, "<==backsql_open_db_handle()\n", 466 0, 0, 0 ); 467 468 return LDAP_SUCCESS; 469 } 470 471 static void *backsql_db_conn_dummy; 472 473 static void 474 backsql_db_conn_keyfree( 475 void *key, 476 void *data ) 477 { 478 (void)backsql_close_db_handle( (SQLHDBC)data ); 479 } 480 481 int 482 backsql_free_db_conn( Operation *op, SQLHDBC dbh ) 483 { 484 Debug( LDAP_DEBUG_TRACE, "==>backsql_free_db_conn()\n", 0, 0, 0 ); 485 486 (void)backsql_close_db_handle( dbh ); 487 ldap_pvt_thread_pool_setkey( op->o_threadctx, 488 &backsql_db_conn_dummy, (void *)SQL_NULL_HDBC, 489 backsql_db_conn_keyfree, NULL, NULL ); 490 491 Debug( LDAP_DEBUG_TRACE, "<==backsql_free_db_conn()\n", 0, 0, 0 ); 492 493 return LDAP_SUCCESS; 494 } 495 496 int 497 backsql_get_db_conn( Operation *op, SQLHDBC *dbhp ) 498 { 499 backsql_info *bi = (backsql_info *)op->o_bd->be_private; 500 int rc = LDAP_SUCCESS; 501 SQLHDBC dbh = SQL_NULL_HDBC; 502 503 Debug( LDAP_DEBUG_TRACE, "==>backsql_get_db_conn()\n", 0, 0, 0 ); 504 505 assert( dbhp != NULL ); 506 *dbhp = SQL_NULL_HDBC; 507 508 if ( op->o_threadctx ) { 509 void *data = NULL; 510 511 ldap_pvt_thread_pool_getkey( op->o_threadctx, 512 &backsql_db_conn_dummy, &data, NULL ); 513 dbh = (SQLHDBC)data; 514 515 } else { 516 dbh = bi->sql_dbh; 517 } 518 519 if ( dbh == SQL_NULL_HDBC ) { 520 rc = backsql_open_db_handle( bi, &dbh ); 521 if ( rc != LDAP_SUCCESS ) { 522 return rc; 523 } 524 525 if ( op->o_threadctx ) { 526 void *data = (void *)dbh; 527 528 ldap_pvt_thread_pool_setkey( op->o_threadctx, 529 &backsql_db_conn_dummy, data, 530 backsql_db_conn_keyfree, NULL, NULL ); 531 532 } else { 533 bi->sql_dbh = dbh; 534 } 535 } 536 537 *dbhp = dbh; 538 539 Debug( LDAP_DEBUG_TRACE, "<==backsql_get_db_conn()\n", 0, 0, 0 ); 540 541 return LDAP_SUCCESS; 542 } 543 544