2 <!DOCTYPE modulesynopsis SYSTEM "../style/modulesynopsis.dtd">
3 <?xml-stylesheet type="text/xsl" href="../style/manual.en.xsl"?>
4 <!-- $LastChangedRevision$ -->
7 Licensed to the Apache Software Foundation (ASF) under one or more
8 contributor license agreements. See the NOTICE file distributed with
9 this work for additional information regarding copyright ownership.
10 The ASF licenses this file to You under the Apache License, Version 2.0
11 (the "License"); you may not use this file except in compliance with
12 the License. You may obtain a copy of the License at
14 http://www.apache.org/licenses/LICENSE-2.0
16 Unless required by applicable law or agreed to in writing, software
17 distributed under the License is distributed on an "AS IS" BASIS,
18 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
19 See the License for the specific language governing permissions and
20 limitations under the License.
23 <modulesynopsis metafile="mod_dbd.xml.meta">
26 <description>Manages SQL database connections</description>
27 <status>Extension</status>
28 <sourcefile>mod_dbd.c</sourcefile>
29 <identifier>dbd_module</identifier>
32 <p><module>mod_dbd</module> manages SQL database connections using
33 <glossary>APR</glossary>. It provides database connections on request
34 to modules requiring SQL database functions, and takes care of
35 managing databases with optimal efficiency and scalability
36 for both threaded and non-threaded MPMs. For details, see the
37 <a href="http://apr.apache.org/">APR</a> website and this overview of the
38 <a href="http://people.apache.org/~niq/dbd.html">Apache DBD Framework</a>
39 by its original developer.
43 <seealso><a href="../misc/password_encryptions.html">Password Formats</a></seealso>
45 <section id="pooling"><title>Connection Pooling</title>
46 <p>This module manages database connections, in a manner
47 optimised for the platform. On non-threaded platforms,
48 it provides a persistent connection in the manner of
49 classic LAMP (Linux, Apache, Mysql, Perl/PHP/Python).
50 On threaded platform, it provides an altogether more
51 scalable and efficient <em>connection pool</em>, as
52 described in <a href="http://www.apachetutor.org/dev/reslist">this
53 article at ApacheTutor</a>. Note that <module>mod_dbd</module>
54 supersedes the modules presented in that article.</p>
57 <section id="connecting"><title>Connecting</title>
59 <p>To connect to your database, you'll need to specify
60 a driver, and connection parameters. These vary from
61 one database engine to another. For example, to connect
62 to mysql, do the following:</p>
64 <highlight language="config">
66 DBDParams host=localhost,dbname=pony,user=shetland,pass=appaloosa
69 <p>You can then use this connection in a variety of other
70 modules, including <module>mod_rewrite</module>,
71 <module>mod_authn_dbd</module>, and <module>mod_lua</module>.
72 Further usage examples appear in each of those modules'
75 <p>See <directive>DBDParams</directive> for connection string
76 information for each of the supported database drivers.</p>
80 <section id="API"><title>Apache DBD API</title>
81 <p><module>mod_dbd</module> exports five functions for other modules
82 to use. The API is as follows:</p>
84 <highlight language="c">
87 apr_dbd_driver_t *driver;
91 /* Export functions to access the database */
93 /* acquire a connection that MUST be explicitly closed.
94 * Returns NULL on error
96 AP_DECLARE(ap_dbd_t*) ap_dbd_open(apr_pool_t*, server_rec*);
98 /* release a connection acquired with ap_dbd_open */
99 AP_DECLARE(void) ap_dbd_close(server_rec*, ap_dbd_t*);
101 /* acquire a connection that will have the lifetime of a request
102 * and MUST NOT be explicitly closed. Return NULL on error.
103 * This is the preferred function for most applications.
105 AP_DECLARE(ap_dbd_t*) ap_dbd_acquire(request_rec*);
107 /* acquire a connection that will have the lifetime of a connection
108 * and MUST NOT be explicitly closed. Return NULL on error.
110 AP_DECLARE(ap_dbd_t*) ap_dbd_cacquire(conn_rec*);
112 /* Prepare a statement for use by a client module */
113 AP_DECLARE(void) ap_dbd_prepare(server_rec*, const char*, const char*);
115 /* Also export them as optional functions for modules that prefer it */
116 APR_DECLARE_OPTIONAL_FN(ap_dbd_t*, ap_dbd_open, (apr_pool_t*, server_rec*));
117 APR_DECLARE_OPTIONAL_FN(void, ap_dbd_close, (server_rec*, ap_dbd_t*));
118 APR_DECLARE_OPTIONAL_FN(ap_dbd_t*, ap_dbd_acquire, (request_rec*));
119 APR_DECLARE_OPTIONAL_FN(ap_dbd_t*, ap_dbd_cacquire, (conn_rec*));
120 APR_DECLARE_OPTIONAL_FN(void, ap_dbd_prepare, (server_rec*, const char*, const char*));
124 <section id="prepared"><title>SQL Prepared Statements</title>
125 <p><module>mod_dbd</module> supports SQL prepared statements on behalf
126 of modules that may wish to use them. Each prepared statement
127 must be assigned a name (label), and they are stored in a hash:
128 the <code>prepared</code> field of an <code>ap_dbd_t</code>.
129 Hash entries are of type <code>apr_dbd_prepared_t</code>
130 and can be used in any of the apr_dbd prepared statement
131 SQL query or select commands.</p>
133 <p>It is up to dbd user modules to use the prepared statements
134 and document what statements can be specified in httpd.conf,
135 or to provide their own directives and use <code>ap_dbd_prepare</code>.</p>
137 <note type="warning"><title>Caveat</title>
138 When using prepared statements with a MySQL database, it is preferred to set
139 <code>reconnect</code> to 0 in the connection string as to avoid errors that
140 arise from the MySQL client reconnecting without properly resetting the
141 prepared statements. If set to 1, any broken connections will be attempted
142 fixed, but as mod_dbd is not informed, the prepared statements will be invalidated.
146 <section id="security">
147 <title>SECURITY WARNING</title>
148 <p>Any web/database application needs to secure itself against SQL
149 injection attacks. In most cases, Apache DBD is safe, because
150 applications use prepared statements, and untrusted inputs are
151 only ever used as data. Of course, if you use it via third-party
152 modules, you should ascertain what precautions they may require.</p>
153 <p>However, the <var>FreeTDS</var> driver is inherently
154 <strong>unsafe</strong>. The underlying library doesn't support
155 prepared statements, so the driver emulates them, and the
156 untrusted input is merged into the SQL statement.</p>
157 <p>It can be made safe by <em>untainting</em> all inputs:
158 a process inspired by Perl's taint checking. Each input
159 is matched against a regexp, and only the match is used,
160 according to the Perl idiom:</p>
162 <pre><code> $untrusted =~ /([a-z]+)/;
163 $trusted = $1;</code></pre>
165 <p>To use this, the untainting regexps must be included in the
166 prepared statements configured. The regexp follows immediately
167 after the % in the prepared statement, and is enclosed in
168 curly brackets {}. For example, if your application expects
169 alphanumeric input, you can use:</p>
171 <code>"SELECT foo FROM bar WHERE input = %s"</code>
173 <p>with other drivers, and suffer nothing worse than a failed query.
174 But with FreeTDS you'd need:</p>
176 <code>"SELECT foo FROM bar WHERE input = %{([A-Za-z0-9]+)}s"</code>
178 <p>Now anything that doesn't match the regexp's $1 match is
179 discarded, so the statement is safe.</p>
180 <p>An alternative to this may be the third-party ODBC driver,
181 which offers the security of genuine prepared statements.</p>
184 <name>DBDriver</name>
185 <description>Specify an SQL driver</description>
186 <syntax>DBDriver <var>name</var></syntax>
187 <contextlist><context>server config</context><context>virtual host</context>
191 <p>Selects an apr_dbd driver by name. The driver must be installed
192 on your system (on most systems, it will be a shared object or dll).
193 For example, <code>DBDriver mysql</code> will select the MySQL
194 driver in apr_dbd_mysql.so.</p>
199 <name>DBDParams</name>
200 <description>Parameters for database connection</description>
202 <var>param1</var>=<var>value1</var>[,<var>param2</var>=<var>value2</var>]</syntax>
203 <contextlist><context>server config</context><context>virtual host</context>
207 <p>As required by the underlying driver. Typically this will be
208 used to pass whatever cannot be defaulted amongst username,
209 password, database name, hostname and port number for connection.</p>
210 <p>Connection string parameters for current drivers include:</p>
212 <dt>FreeTDS (for MSSQL and SyBase)</dt>
213 <dd>username, password, appname, dbname, host, charset, lang, server</dd>
215 <dd>host, port, user, pass, dbname, sock, flags, fldsz, group, reconnect</dd>
217 <dd>user, pass, dbname, server</dd>
219 <dd>The connection string is passed straight through to <code>PQconnectdb</code></dd>
221 <dd>The connection string is split on a colon, and <code>part1:part2</code> is used as <code>sqlite_open(part1, atoi(part2), NULL)</code></dd>
223 <dd>The connection string is passed straight through to <code>sqlite3_open</code></dd>
225 <dd>datasource, user, password, connect, ctimeout, stimeout, access, txmode, bufsize</dd>
231 <name>DBDPersist</name>
232 <description>Whether to use persistent connections</description>
233 <syntax>DBDPersist On|Off</syntax>
234 <contextlist><context>server config</context><context>virtual host</context>
238 <p>If set to Off, persistent and pooled connections are disabled.
239 A new database connection is opened when requested by a client,
240 and closed immediately on release. This option is for debugging
241 and low-usage servers.</p>
243 <p>The default is to enable a pool of persistent connections
244 (or a single LAMP-style persistent connection in the case of a
245 non-threaded server), and should almost always be used in operation.</p>
247 <p>Prior to version 2.2.2, this directive accepted only the values
248 <code>0</code> and <code>1</code> instead of <code>Off</code> and
249 <code>On</code>, respectively.</p>
254 <name>DBDPrepareSQL</name>
255 <description>Define an SQL prepared statement</description>
256 <syntax>DBDPrepareSQL <var>"SQL statement"</var> <var>label</var></syntax>
257 <contextlist><context>server config</context><context>virtual host</context>
261 <p>For modules such as authentication that repeatedly use a
262 single SQL statement, optimum performance is achieved by preparing
263 the statement at startup rather than every time it is used.
264 This directive prepares an SQL statement and assigns it a label.</p>
270 <description>Minimum number of connections</description>
271 <syntax>DBDMin <var>number</var></syntax>
272 <default>DBDMin 1</default>
273 <contextlist><context>server config</context><context>virtual host</context>
277 <p>Set the minimum number of connections per process (threaded
284 <description>Maximum sustained number of connections</description>
285 <syntax>DBDKeep <var>number</var></syntax>
286 <default>DBDKeep 2</default>
287 <contextlist><context>server config</context><context>virtual host</context>
291 <p>Set the maximum number of connections per process to be
292 sustained, other than for handling peak demand (threaded
299 <description>Maximum number of connections</description>
300 <syntax>DBDMax <var>number</var></syntax>
301 <default>DBDMax 10</default>
302 <contextlist><context>server config</context><context>virtual host</context>
306 <p>Set the hard maximum number of connections per process
307 (threaded platforms only).</p>
312 <name>DBDExptime</name>
313 <description>Keepalive time for idle connections</description>
314 <syntax>DBDExptime <var>time-in-seconds</var></syntax>
315 <default>DBDExptime 300</default>
316 <contextlist><context>server config</context><context>virtual host</context>
320 <p>Set the time to keep idle connections alive when the number
321 of connections specified in DBDKeep has been exceeded (threaded
327 <name>DBDInitSQL</name>
328 <description>Execute an SQL statement after connecting to a database</description>
329 <syntax>DBDInitSQL <var>"SQL statement"</var></syntax>
330 <contextlist><context>server config</context><context>virtual host</context>
334 <p>Modules, that wish it, can have one or more SQL statements
335 executed when a connection to a database is created. Example
336 usage could be initializing certain values or adding a log
337 entry when a new connection is made to the database.</p>