1 /* statement.c - the statement type
2 *
3 * Copyright (C) 2005-2010 Gerhard Häring <gh@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
pysqlite_statement_create(pysqlite_Statement * self,pysqlite_Connection * connection,PyObject * sql)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
pysqlite_statement_bind_parameter(pysqlite_Statement * self,int pos,PyObject * parameter)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 */
_need_adapt(PyObject * obj)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
pysqlite_statement_bind_parameters(pysqlite_Statement * self,PyObject * parameters)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
pysqlite_statement_recompile(pysqlite_Statement * self,PyObject * params)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
pysqlite_statement_finalize(pysqlite_Statement * self)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
pysqlite_statement_reset(pysqlite_Statement * self)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
pysqlite_statement_mark_dirty(pysqlite_Statement * self)404 void pysqlite_statement_mark_dirty(pysqlite_Statement* self)
405 {
406 self->in_use = 1;
407 }
408
pysqlite_statement_dealloc(pysqlite_Statement * self)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 */
pysqlite_check_remaining_sql(const char * tail)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
pysqlite_statement_setup_types(void)540 extern int pysqlite_statement_setup_types(void)
541 {
542 pysqlite_StatementType.tp_new = PyType_GenericNew;
543 return PyType_Ready(&pysqlite_StatementType);
544 }
545