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