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 #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
pysqlite_statement_create(pysqlite_Statement * self,pysqlite_Connection * connection,PyObject * sql)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
pysqlite_statement_bind_parameter(pysqlite_Statement * self,int pos,PyObject * parameter,int allow_8bit_chars)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 */
_need_adapt(PyObject * obj)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
pysqlite_statement_bind_parameters(pysqlite_Statement * self,PyObject * parameters,int allow_8bit_chars)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
pysqlite_statement_recompile(pysqlite_Statement * self,PyObject * params)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
pysqlite_statement_finalize(pysqlite_Statement * self)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
pysqlite_statement_reset(pysqlite_Statement * self)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
pysqlite_statement_mark_dirty(pysqlite_Statement * self)408 void pysqlite_statement_mark_dirty(pysqlite_Statement* self)
409 {
410 self->in_use = 1;
411 }
412
pysqlite_statement_dealloc(pysqlite_Statement * self)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 */
pysqlite_check_remaining_sql(const char * tail)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
pysqlite_statement_setup_types(void)544 extern int pysqlite_statement_setup_types(void)
545 {
546 pysqlite_StatementType.tp_new = PyType_GenericNew;
547 return PyType_Ready(&pysqlite_StatementType);
548 }
549