1 /* statement.c - the statement type 2 * 3 * Copyright (C) 2005-2010 Gerhard Hring <gh (at) ghaering.de> 4 * 5 * This file is part of pysqlite. 6 * 7 * This software is provided 'as-is', without any express or implied 8 * warranty. In no event will the authors be held liable for any damages 9 * arising from the use of this software. 10 * 11 * Permission is granted to anyone to use this software for any purpose, 12 * including commercial applications, and to alter it and redistribute it 13 * freely, subject to the following restrictions: 14 * 15 * 1. The origin of this software must not be misrepresented; you must not 16 * claim that you wrote the original software. If you use this software 17 * in a product, an acknowledgment in the product documentation would be 18 * appreciated but is not required. 19 * 2. Altered source versions must be plainly marked as such, and must not be 20 * misrepresented as being the original software. 21 * 3. This notice may not be removed or altered from any source distribution. 22 */ 23 24 #include "statement.h" 25 #include "cursor.h" 26 #include "connection.h" 27 #include "microprotocols.h" 28 #include "prepare_protocol.h" 29 #include "util.h" 30 #include "sqlitecompat.h" 31 32 /* prototypes */ 33 static int pysqlite_check_remaining_sql(const char* tail); 34 35 typedef enum { 36 LINECOMMENT_1, 37 IN_LINECOMMENT, 38 COMMENTSTART_1, 39 IN_COMMENT, 40 COMMENTEND_1, 41 NORMAL 42 } parse_remaining_sql_state; 43 44 typedef enum { 45 TYPE_INT, 46 TYPE_LONG, 47 TYPE_FLOAT, 48 TYPE_STRING, 49 TYPE_UNICODE, 50 TYPE_BUFFER, 51 TYPE_UNKNOWN 52 } parameter_type; 53 54 int pysqlite_statement_create(pysqlite_Statement* self, pysqlite_Connection* connection, PyObject* sql) 55 { 56 const char* tail; 57 int rc; 58 PyObject* sql_str; 59 char* sql_cstr; 60 61 self->st = NULL; 62 self->in_use = 0; 63 64 if (PyString_Check(sql)) { 65 sql_str = sql; 66 Py_INCREF(sql_str); 67 } else if (PyUnicode_Check(sql)) { 68 sql_str = PyUnicode_AsUTF8String(sql); 69 if (!sql_str) { 70 rc = PYSQLITE_SQL_WRONG_TYPE; 71 return rc; 72 } 73 } else { 74 rc = PYSQLITE_SQL_WRONG_TYPE; 75 return rc; 76 } 77 sql_cstr = PyString_AS_STRING(sql_str); 78 if (strlen(sql_cstr) != (size_t)PyString_GET_SIZE(sql_str)) { 79 Py_DECREF(sql_str); 80 PyErr_SetString(PyExc_ValueError, "the query contains a null character"); 81 return PYSQLITE_SQL_WRONG_TYPE; 82 } 83 84 self->in_weakreflist = NULL; 85 self->sql = sql_str; 86 87 Py_BEGIN_ALLOW_THREADS 88 rc = sqlite3_prepare(connection->db, 89 sql_cstr, 90 -1, 91 &self->st, 92 &tail); 93 Py_END_ALLOW_THREADS 94 95 self->db = connection->db; 96 97 if (rc == SQLITE_OK && pysqlite_check_remaining_sql(tail)) { 98 (void)sqlite3_finalize(self->st); 99 self->st = NULL; 100 rc = PYSQLITE_TOO_MUCH_SQL; 101 } 102 103 return rc; 104 } 105 106 int pysqlite_statement_bind_parameter(pysqlite_Statement* self, int pos, PyObject* parameter, int allow_8bit_chars) 107 { 108 int rc = SQLITE_OK; 109 const char* buffer; 110 char* string; 111 Py_ssize_t buflen; 112 PyObject* stringval; 113 parameter_type paramtype; 114 char* c; 115 116 if (parameter == Py_None) { 117 rc = sqlite3_bind_null(self->st, pos); 118 goto final; 119 } 120 121 if (PyInt_CheckExact(parameter)) { 122 paramtype = TYPE_INT; 123 } else if (PyLong_CheckExact(parameter)) { 124 paramtype = TYPE_LONG; 125 } else if (PyFloat_CheckExact(parameter)) { 126 paramtype = TYPE_FLOAT; 127 } else if (PyString_CheckExact(parameter)) { 128 paramtype = TYPE_STRING; 129 } else if (PyUnicode_CheckExact(parameter)) { 130 paramtype = TYPE_UNICODE; 131 } else if (PyBuffer_Check(parameter)) { 132 paramtype = TYPE_BUFFER; 133 } else if (PyInt_Check(parameter)) { 134 paramtype = TYPE_INT; 135 } else if (PyLong_Check(parameter)) { 136 paramtype = TYPE_LONG; 137 } else if (PyFloat_Check(parameter)) { 138 paramtype = TYPE_FLOAT; 139 } else if (PyString_Check(parameter)) { 140 paramtype = TYPE_STRING; 141 } else if (PyUnicode_Check(parameter)) { 142 paramtype = TYPE_UNICODE; 143 } else { 144 paramtype = TYPE_UNKNOWN; 145 } 146 147 if (paramtype == TYPE_STRING && !allow_8bit_chars) { 148 string = PyString_AS_STRING(parameter); 149 for (c = string; *c != 0; c++) { 150 if (*c & 0x80) { 151 PyErr_SetString(pysqlite_ProgrammingError, "You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings (like text_factory = str). It is highly recommended that you instead just switch your application to Unicode strings."); 152 rc = -1; 153 goto final; 154 } 155 } 156 } 157 158 switch (paramtype) { 159 case TYPE_INT: { 160 long longval = PyInt_AsLong(parameter); 161 rc = sqlite3_bind_int64(self->st, pos, longval); 162 break; 163 } 164 case TYPE_LONG: { 165 sqlite_int64 value = _pysqlite_long_as_int64(parameter); 166 if (value == -1 && PyErr_Occurred()) 167 rc = -1; 168 else 169 rc = sqlite3_bind_int64(self->st, pos, (sqlite_int64)value); 170 break; 171 } 172 case TYPE_FLOAT: 173 rc = sqlite3_bind_double(self->st, pos, PyFloat_AsDouble(parameter)); 174 break; 175 case TYPE_STRING: 176 PyString_AsStringAndSize(parameter, &string, &buflen); 177 rc = sqlite3_bind_text(self->st, pos, string, buflen, SQLITE_TRANSIENT); 178 break; 179 case TYPE_UNICODE: 180 stringval = PyUnicode_AsUTF8String(parameter); 181 PyString_AsStringAndSize(stringval, &string, &buflen); 182 rc = sqlite3_bind_text(self->st, pos, string, buflen, SQLITE_TRANSIENT); 183 Py_DECREF(stringval); 184 break; 185 case TYPE_BUFFER: 186 if (PyObject_AsCharBuffer(parameter, &buffer, &buflen) == 0) { 187 rc = sqlite3_bind_blob(self->st, pos, buffer, buflen, SQLITE_TRANSIENT); 188 } else { 189 PyErr_SetString(PyExc_ValueError, "could not convert BLOB to buffer"); 190 rc = -1; 191 } 192 break; 193 case TYPE_UNKNOWN: 194 rc = -1; 195 } 196 197 final: 198 return rc; 199 } 200 201 /* returns 0 if the object is one of Python's internal ones that don't need to be adapted */ 202 static int _need_adapt(PyObject* obj) 203 { 204 if (pysqlite_BaseTypeAdapted) { 205 return 1; 206 } 207 208 if (PyInt_CheckExact(obj) || PyLong_CheckExact(obj) 209 || PyFloat_CheckExact(obj) || PyString_CheckExact(obj) 210 || PyUnicode_CheckExact(obj) || PyBuffer_Check(obj)) { 211 return 0; 212 } else { 213 return 1; 214 } 215 } 216 217 void pysqlite_statement_bind_parameters(pysqlite_Statement* self, PyObject* parameters, int allow_8bit_chars) 218 { 219 PyObject* current_param; 220 PyObject* adapted; 221 const char* binding_name; 222 int i; 223 int rc; 224 int num_params_needed; 225 int num_params; 226 227 Py_BEGIN_ALLOW_THREADS 228 num_params_needed = sqlite3_bind_parameter_count(self->st); 229 Py_END_ALLOW_THREADS 230 231 if (PyTuple_CheckExact(parameters) || PyList_CheckExact(parameters) || (!PyDict_Check(parameters) && PySequence_Check(parameters))) { 232 /* parameters passed as sequence */ 233 if (PyTuple_CheckExact(parameters)) { 234 num_params = PyTuple_GET_SIZE(parameters); 235 } else if (PyList_CheckExact(parameters)) { 236 num_params = PyList_GET_SIZE(parameters); 237 } else { 238 num_params = PySequence_Size(parameters); 239 } 240 if (num_params != num_params_needed) { 241 PyErr_Format(pysqlite_ProgrammingError, "Incorrect number of bindings supplied. The current statement uses %d, and there are %d supplied.", 242 num_params_needed, num_params); 243 return; 244 } 245 for (i = 0; i < num_params; i++) { 246 if (PyTuple_CheckExact(parameters)) { 247 current_param = PyTuple_GET_ITEM(parameters, i); 248 Py_XINCREF(current_param); 249 } else if (PyList_CheckExact(parameters)) { 250 current_param = PyList_GET_ITEM(parameters, i); 251 Py_XINCREF(current_param); 252 } else { 253 current_param = PySequence_GetItem(parameters, i); 254 } 255 if (!current_param) { 256 return; 257 } 258 259 if (!_need_adapt(current_param)) { 260 adapted = current_param; 261 } else { 262 adapted = pysqlite_microprotocols_adapt(current_param, (PyObject*)&pysqlite_PrepareProtocolType, NULL); 263 if (adapted) { 264 Py_DECREF(current_param); 265 } else { 266 PyErr_Clear(); 267 adapted = current_param; 268 } 269 } 270 271 rc = pysqlite_statement_bind_parameter(self, i + 1, adapted, allow_8bit_chars); 272 Py_DECREF(adapted); 273 274 if (rc != SQLITE_OK) { 275 if (!PyErr_Occurred()) { 276 PyErr_Format(pysqlite_InterfaceError, "Error binding parameter %d - probably unsupported type.", i); 277 } 278 return; 279 } 280 } 281 } else if (PyDict_Check(parameters)) { 282 /* parameters passed as dictionary */ 283 for (i = 1; i <= num_params_needed; i++) { 284 Py_BEGIN_ALLOW_THREADS 285 binding_name = sqlite3_bind_parameter_name(self->st, i); 286 Py_END_ALLOW_THREADS 287 if (!binding_name) { 288 PyErr_Format(pysqlite_ProgrammingError, "Binding %d has no name, but you supplied a dictionary (which has only names).", i); 289 return; 290 } 291 292 binding_name++; /* skip first char (the colon) */ 293 if (PyDict_CheckExact(parameters)) { 294 current_param = PyDict_GetItemString(parameters, binding_name); 295 Py_XINCREF(current_param); 296 } else { 297 current_param = PyMapping_GetItemString(parameters, (char*)binding_name); 298 } 299 if (!current_param) { 300 PyErr_Format(pysqlite_ProgrammingError, "You did not supply a value for binding %d.", i); 301 return; 302 } 303 304 if (!_need_adapt(current_param)) { 305 adapted = current_param; 306 } else { 307 adapted = pysqlite_microprotocols_adapt(current_param, (PyObject*)&pysqlite_PrepareProtocolType, NULL); 308 if (adapted) { 309 Py_DECREF(current_param); 310 } else { 311 PyErr_Clear(); 312 adapted = current_param; 313 } 314 } 315 316 rc = pysqlite_statement_bind_parameter(self, i, adapted, allow_8bit_chars); 317 Py_DECREF(adapted); 318 319 if (rc != SQLITE_OK) { 320 if (!PyErr_Occurred()) { 321 PyErr_Format(pysqlite_InterfaceError, "Error binding parameter :%s - probably unsupported type.", binding_name); 322 } 323 return; 324 } 325 } 326 } else { 327 PyErr_SetString(PyExc_ValueError, "parameters are of unsupported type"); 328 } 329 } 330 331 int pysqlite_statement_recompile(pysqlite_Statement* self, PyObject* params) 332 { 333 const char* tail; 334 int rc; 335 char* sql_cstr; 336 sqlite3_stmt* new_st; 337 338 sql_cstr = PyString_AsString(self->sql); 339 340 Py_BEGIN_ALLOW_THREADS 341 rc = sqlite3_prepare(self->db, 342 sql_cstr, 343 -1, 344 &new_st, 345 &tail); 346 Py_END_ALLOW_THREADS 347 348 if (rc == SQLITE_OK) { 349 /* The efficient sqlite3_transfer_bindings is only available in SQLite 350 * version 3.2.2 or later. For older SQLite releases, that might not 351 * even define SQLITE_VERSION_NUMBER, we do it the manual way. 352 */ 353 #ifdef SQLITE_VERSION_NUMBER 354 #if SQLITE_VERSION_NUMBER >= 3002002 355 /* The check for the number of parameters is necessary to not trigger a 356 * bug in certain SQLite versions (experienced in 3.2.8 and 3.3.4). */ 357 if (sqlite3_bind_parameter_count(self->st) > 0) { 358 (void)sqlite3_transfer_bindings(self->st, new_st); 359 } 360 #endif 361 #else 362 statement_bind_parameters(self, params); 363 #endif 364 365 (void)sqlite3_finalize(self->st); 366 self->st = new_st; 367 } 368 369 return rc; 370 } 371 372 int pysqlite_statement_finalize(pysqlite_Statement* self) 373 { 374 int rc; 375 376 rc = SQLITE_OK; 377 if (self->st) { 378 Py_BEGIN_ALLOW_THREADS 379 rc = sqlite3_finalize(self->st); 380 Py_END_ALLOW_THREADS 381 self->st = NULL; 382 } 383 384 self->in_use = 0; 385 386 return rc; 387 } 388 389 int pysqlite_statement_reset(pysqlite_Statement* self) 390 { 391 int rc; 392 393 rc = SQLITE_OK; 394 395 if (self->in_use && self->st) { 396 Py_BEGIN_ALLOW_THREADS 397 rc = sqlite3_reset(self->st); 398 Py_END_ALLOW_THREADS 399 400 if (rc == SQLITE_OK) { 401 self->in_use = 0; 402 } 403 } 404 405 return rc; 406 } 407 408 void pysqlite_statement_mark_dirty(pysqlite_Statement* self) 409 { 410 self->in_use = 1; 411 } 412 413 void pysqlite_statement_dealloc(pysqlite_Statement* self) 414 { 415 if (self->st) { 416 Py_BEGIN_ALLOW_THREADS 417 sqlite3_finalize(self->st); 418 Py_END_ALLOW_THREADS 419 } 420 421 self->st = NULL; 422 423 Py_XDECREF(self->sql); 424 425 if (self->in_weakreflist != NULL) { 426 PyObject_ClearWeakRefs((PyObject*)self); 427 } 428 429 Py_TYPE(self)->tp_free((PyObject*)self); 430 } 431 432 /* 433 * Checks if there is anything left in an SQL string after SQLite compiled it. 434 * This is used to check if somebody tried to execute more than one SQL command 435 * with one execute()/executemany() command, which the DB-API and we don't 436 * allow. 437 * 438 * Returns 1 if there is more left than should be. 0 if ok. 439 */ 440 static int pysqlite_check_remaining_sql(const char* tail) 441 { 442 const char* pos = tail; 443 444 parse_remaining_sql_state state = NORMAL; 445 446 for (;;) { 447 switch (*pos) { 448 case 0: 449 return 0; 450 case '-': 451 if (state == NORMAL) { 452 state = LINECOMMENT_1; 453 } else if (state == LINECOMMENT_1) { 454 state = IN_LINECOMMENT; 455 } 456 break; 457 case ' ': 458 case '\t': 459 break; 460 case '\n': 461 case 13: 462 if (state == IN_LINECOMMENT) { 463 state = NORMAL; 464 } 465 break; 466 case '/': 467 if (state == NORMAL) { 468 state = COMMENTSTART_1; 469 } else if (state == COMMENTEND_1) { 470 state = NORMAL; 471 } else if (state == COMMENTSTART_1) { 472 return 1; 473 } 474 break; 475 case '*': 476 if (state == NORMAL) { 477 return 1; 478 } else if (state == LINECOMMENT_1) { 479 return 1; 480 } else if (state == COMMENTSTART_1) { 481 state = IN_COMMENT; 482 } else if (state == IN_COMMENT) { 483 state = COMMENTEND_1; 484 } 485 break; 486 default: 487 if (state == COMMENTEND_1) { 488 state = IN_COMMENT; 489 } else if (state == IN_LINECOMMENT) { 490 } else if (state == IN_COMMENT) { 491 } else { 492 return 1; 493 } 494 } 495 496 pos++; 497 } 498 499 return 0; 500 } 501 502 PyTypeObject pysqlite_StatementType = { 503 PyVarObject_HEAD_INIT(NULL, 0) 504 MODULE_NAME ".Statement", /* tp_name */ 505 sizeof(pysqlite_Statement), /* tp_basicsize */ 506 0, /* tp_itemsize */ 507 (destructor)pysqlite_statement_dealloc, /* tp_dealloc */ 508 0, /* tp_print */ 509 0, /* tp_getattr */ 510 0, /* tp_setattr */ 511 0, /* tp_compare */ 512 0, /* tp_repr */ 513 0, /* tp_as_number */ 514 0, /* tp_as_sequence */ 515 0, /* tp_as_mapping */ 516 0, /* tp_hash */ 517 0, /* tp_call */ 518 0, /* tp_str */ 519 0, /* tp_getattro */ 520 0, /* tp_setattro */ 521 0, /* tp_as_buffer */ 522 Py_TPFLAGS_DEFAULT | Py_TPFLAGS_HAVE_WEAKREFS, /* tp_flags */ 523 0, /* tp_doc */ 524 0, /* tp_traverse */ 525 0, /* tp_clear */ 526 0, /* tp_richcompare */ 527 offsetof(pysqlite_Statement, in_weakreflist), /* tp_weaklistoffset */ 528 0, /* tp_iter */ 529 0, /* tp_iternext */ 530 0, /* tp_methods */ 531 0, /* tp_members */ 532 0, /* tp_getset */ 533 0, /* tp_base */ 534 0, /* tp_dict */ 535 0, /* tp_descr_get */ 536 0, /* tp_descr_set */ 537 0, /* tp_dictoffset */ 538 (initproc)0, /* tp_init */ 539 0, /* tp_alloc */ 540 0, /* tp_new */ 541 0 /* tp_free */ 542 }; 543 544 extern int pysqlite_statement_setup_types(void) 545 { 546 pysqlite_StatementType.tp_new = PyType_GenericNew; 547 return PyType_Ready(&pysqlite_StatementType); 548 } 549