select-statement a [[param &1 = c], d,e, f,g,h,i,j], default k,l,m,n dbAlias = b

Group

Basic Functions


Executes an SQL statement a on the database being represented by alias b. Placeholders in the form of &parameterNumber being defined in the statement are replaced by the values of parameters c to j. Characters that are needed for a valid syntax need to be placed in the statement. The number of selected rows is limited to 1 automatically.

If the database returns a result the value of the first column is returned. Otherwise, k is returned.

Is a column being selected of type BLOB, the parameter m allows to specify if the data being selected should be encoded with Base64 before being added to the result list. If m is true, the function performs an encoding, false will leave the data unchanged. If there are no columns of type BLOB, the parameter has no effect.

Important note: In addition to the use of &parameterNumber for parameters it is possible to use the naming scheme for 'prepared statements'. The use of this prevents SQL Injection Attacks and should be preferred.

Description of Parameters


Parameter

Description

a

SQL statement to be called.

b

The database alias. A database alias stands for a configured connection to a database system (see configuration file ./etc/database.xml and section DatabaseService).

c

(optional) Parameter &1 of the statement.

d

(optional) Parameter &2 of the statement.

e

(optional) Parameter &3 of the statement.

f

(optional) Parameter &4 of the statement.

g

(optional) Parameter &5 of the statement.

h

(optional) Parameter &6 of the statement.

i

(optional) Parameter &7 of the statement.

j

(optional) Parameter &8 of the statement.

k

(optional)Value to be returned if the database does not return a result.

l

(optional) true if data in columns of type BLOB should be encoded with Base64. Default: false.

m

(optional) true for read-only mode.

n

(optional) If true, SQL errors coming from the database are ignored. Default: <empty> (== false).

Examples


Assume a map zipcodes containing all zip codes of Germany (imaginary 20000 entries).


Parameter a

b

c

d…j

k

l

m

n

Result

select zipcode from zipcodes

testdb



unknown




01067

select city from zipcodes where zipcode = &1

testdb

80336


unknown




Munich

select city from zipcodes where zipcode = &1

testdb

00000


unknown




unknown

select pin from atmdata where username = '&1' and passwd = '&2'

testdb

root

geheim

unknown




4711

select pin from atmdata where username = '&1' and passwd = '&2'

testdb

root

' or ''='

unknown




4711

select pin from atmdata where username = @1:s@ and passwd = @2:s@

testdb

root

' or ''='

unknown




unknown