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