Vely logo Empower C
install     tutorials     examples     documentation     license     about

11.0.11 released on Jul 25, 2022

Database queries



DESCRIPTION:



Query interface, SQL injection


Vely has the same query interface for all databases (see for example run-query). It provides means to execute dynamic as well as static queries; regardless, query input parameters are always separated from the query logic. This separation is in some cases emulated (see run-query, start-query), and in others native, such as with prepared statements (see run-query, start-query). In either case, Vely provides protection against SQL injections.

Queries with user input


User input should be provided to queries only by using run-query, start-query. Do not execute dynamic queries with user provided data with exec-query, which is meant for static queries that do not return a result set, generally DDL (Data Definition Language) statements such as CREATE TABLE for instance.

Input and output parameters


Input parameters and output resuls are always null-delimited strings. In web applications, more often than not, it is string values that are sought as output or provided as input, and conversion to other types rarely happens; there is surely a number of applications where the opposite may be true, but for the majority of applications this approach may work better. If data conversion is needed, it can be performed pre-query or post-query, depending on the purpose. The placeholder for input parameter is chosen to represent this approach, using single quotes (as text literals are single quoted in SQL) and %s (a C convention for string placeholders), thus '%s' is used for input parameters.

Binary data


Binary data can be handled for storage or retrieval via hexadecimal strings (see hex-encode, hex-decode), or Base64 (see base64-decode, base64-encode); this is reasonable for smaller amounts of data. Note that storing large amounts of binary data in the database is generally less desireable than storing such data (for instance PDF or JPG documents) in the file system (see file_storage), as manipulating and retrieving binary data is generally easier and faster that way.

Prepared statements


Prepared statements typically provide better performance but may not be ideal in every circumstance (see prepared_statements).

Multiple statements


Multiple statements in one query can be executed in PostgreSQL, where only the last statement's result set is available, which is simpler and probably more manageable. MariaDB multiple statement execution is disabled by default, and so is for SQLite. Multiple statement execution isn't ideal as generally the number of statements may not be known in advance and retrieving multiple results sets can be challenging. Using stored procedures that return a single result set, or multiple queries to execute multiple statements are both better approach.

Prepared queries, regardless of the database, can never be multiple statements, which is a limitation that exists in all databases.

Connection persistence and reuse


A single Vely process (see how_vely_works and vely_architecture) keeps a single connection open to the database, which is shared between all sequential requests a single process will handle. Each request will either commit or rollback a transaction; if the transaction is neither committed nor rollback-ed, it will be rollback-ed (see commit-transaction, rollback-transaction). If a database connection is lost, Vely will automatically re-establish it if possible. This allows for very fast query execution and essentially unlimited reuse of prepared statemens.

SEE ALSO:


Database ( on-error   affected-rows   error-text   error   begin-transaction   column-count   column-data   database_queries   column-names   commit-transaction   prepared_statements   current-row   database_config_file   define-query   exec-query   loop-query   query-result   rollback-transaction   row-count   run-query   start-query  )  SEE ALL (documentation)



Copyright (c) 2022 DaSoftver LLC. Vely is a trademark of Dasoftver LLC. The software and information herein are provided "AS IS" and without any warranties or guarantees of any kind. This web page is licensed under CC-BY-SA-4.0.