/usr/share/doc/racket/db/using-db.html is in racket-doc 6.7-3.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 | <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html><head><meta http-equiv="content-type" content="text/html; charset=utf-8"/><title>1 Using Database Connections</title><link rel="stylesheet" type="text/css" href="../scribble.css" title="default"/><link rel="stylesheet" type="text/css" href="../racket.css" title="default"/><link rel="stylesheet" type="text/css" href="../manual-style.css" title="default"/><link rel="stylesheet" type="text/css" href="../manual-racket.css" title="default"/><link rel="stylesheet" type="text/css" href="../doc-site.css" title="default"/><script type="text/javascript" src="../scribble-common.js"></script><script type="text/javascript" src="../manual-racket.js"></script><script type="text/javascript" src="../doc-site.js"></script><script type="text/javascript" src="../local-redirect/local-redirect.js"></script><script type="text/javascript" src="../local-redirect/local-user-redirect.js"></script><!--[if IE 6]><style type="text/css">.SIEHidden { overflow: hidden; }</style><![endif]--></head><body id="doc-racket-lang-org"><div class="tocset"><div class="tocview"><div class="tocviewlist tocviewlisttopspace"><div class="tocviewtitle"><table cellspacing="0" cellpadding="0"><tr><td style="width: 1em;"><a href="javascript:void(0);" title="Expand/Collapse" class="tocviewtoggle" onclick="TocviewToggle(this,"tocview_0");">▼</a></td><td></td><td><a href="index.html" class="tocviewlink" data-pltdoc="x">DB:<span class="mywbr"> </span> Database Connectivity</a></td></tr></table></div><div class="tocviewsublisttop" style="display: block;" id="tocview_0"><table cellspacing="0" cellpadding="0"><tr><td align="right">1 </td><td><a href="" class="tocviewselflink" data-pltdoc="x">Using Database Connections</a></td></tr><tr><td align="right">2 </td><td><a href="connect.html" class="tocviewlink" data-pltdoc="x">Connections</a></td></tr><tr><td align="right">3 </td><td><a href="query-api.html" class="tocviewlink" data-pltdoc="x">Queries</a></td></tr><tr><td align="right">4 </td><td><a href="sql-types.html" class="tocviewlink" data-pltdoc="x">SQL Types and Conversions</a></td></tr><tr><td align="right">5 </td><td><a href="util.html" class="tocviewlink" data-pltdoc="x">Utilities</a></td></tr><tr><td align="right">6 </td><td><a href="notes.html" class="tocviewlink" data-pltdoc="x">Notes</a></td></tr></table></div></div><div class="tocviewlist"><table cellspacing="0" cellpadding="0"><tr><td style="width: 1em;"><a href="javascript:void(0);" title="Expand/Collapse" class="tocviewtoggle" onclick="TocviewToggle(this,"tocview_1");">►</a></td><td>1 </td><td><a href="" class="tocviewselflink" data-pltdoc="x">Using Database Connections</a></td></tr></table><div class="tocviewsublistbottom" style="display: none;" id="tocview_1"><table cellspacing="0" cellpadding="0"><tr><td align="right">1.1 </td><td><a href="#%28part._intro-basic%29" class="tocviewlink" data-pltdoc="x">Introduction to Using Database Connections</a></td></tr><tr><td align="right">1.2 </td><td><a href="#%28part._dbsec%29" class="tocviewlink" data-pltdoc="x">Database Security</a></td></tr><tr><td align="right">1.3 </td><td><a href="#%28part._dbperf%29" class="tocviewlink" data-pltdoc="x">Database Performance</a></td></tr><tr><td align="right">1.4 </td><td><a href="#%28part._intro-servlets%29" class="tocviewlink" data-pltdoc="x">Databases and Web Servlets</a></td></tr></table></div></div></div><div class="tocsub"><div class="tocsubtitle">On this page:</div><table class="tocsublist" cellspacing="0"><tr><td><span class="tocsublinknumber">1.1<tt> </tt></span><a href="#%28part._intro-basic%29" class="tocsubseclink" data-pltdoc="x">Introduction to Using Database Connections</a></td></tr><tr><td><span class="tocsublinknumber">1.2<tt> </tt></span><a href="#%28part._dbsec%29" class="tocsubseclink" data-pltdoc="x">Database Security</a></td></tr><tr><td><span class="tocsublinknumber">1.2.1<tt> </tt></span><a href="#%28part._dbsec-sql-injection%29" class="tocsubseclink" data-pltdoc="x">SQL Injection</a></td></tr><tr><td><span class="tocsublinknumber">1.2.2<tt> </tt></span><a href="#%28part._dbsec-xss%29" class="tocsubseclink" data-pltdoc="x">Cross-<wbr></wbr>site Scripting (XSS)</a></td></tr><tr><td><span class="tocsublinknumber">1.2.3<tt> </tt></span><a href="#%28part._dbsec-connect%29" class="tocsubseclink" data-pltdoc="x">Making Database Connections Securely</a></td></tr><tr><td><span class="tocsublinknumber">1.3<tt> </tt></span><a href="#%28part._dbperf%29" class="tocsubseclink" data-pltdoc="x">Database Performance</a></td></tr><tr><td><span class="tocsublinknumber">1.3.1<tt> </tt></span><a href="#%28part._dbperf-n%2B1%29" class="tocsubseclink" data-pltdoc="x">The N+<span class="mywbr"> </span>1 Selects Problem</a></td></tr><tr><td><span class="tocsublinknumber">1.3.2<tt> </tt></span><a href="#%28part._dbperf-update-tx%29" class="tocsubseclink" data-pltdoc="x">Updates and Transactions</a></td></tr><tr><td><span class="tocsublinknumber">1.3.3<tt> </tt></span><a href="#%28part._dbperf-pstcache%29" class="tocsubseclink" data-pltdoc="x">Statement Caching</a></td></tr><tr><td><span class="tocsublinknumber">1.3.4<tt> </tt></span><a href="#%28part._dbperf-testing%29" class="tocsubseclink" data-pltdoc="x">Testing Performance of Database-<wbr></wbr>Backed Programs</a></td></tr><tr><td><span class="tocsublinknumber">1.3.5<tt> </tt></span><a href="#%28part._dbperf-concurrency%29" class="tocsubseclink" data-pltdoc="x">Transactions and Concurrency</a></td></tr><tr><td><span class="tocsublinknumber">1.4<tt> </tt></span><a href="#%28part._intro-servlets%29" class="tocsubseclink" data-pltdoc="x">Databases and Web Servlets</a></td></tr></table></div></div><div class="maincolumn"><div class="main"><div class="navsettop"><span class="navleft"><form class="searchform"><input class="searchbox" style="color: #888;" type="text" value="...search manuals..." title="Enter a search string to search the manuals" onkeypress="return DoSearchKey(event, this, "6.7", "../");" onfocus="this.style.color="black"; this.style.textAlign="left"; if (this.value == "...search manuals...") this.value="";" onblur="if (this.value.match(/^ *$/)) { this.style.color="#888"; this.style.textAlign="center"; this.value="...search manuals..."; }"/></form> <a href="../index.html" title="up to the documentation top" data-pltdoc="x" onclick="return GotoPLTRoot("6.7");">top</a></span><span class="navright"> <a href="index.html" title="backward to "DB: Database Connectivity"" data-pltdoc="x">← prev</a> <a href="index.html" title="up to "DB: Database Connectivity"" data-pltdoc="x">up</a> <a href="connect.html" title="forward to "2 Connections"" data-pltdoc="x">next →</a></span> </div><h3 x-source-module="(lib "db/scribblings/db.scrbl")" x-source-pkg="db-doc" x-part-tag=""using-db"">1<tt> </tt><a name="(part._using-db)"></a>Using Database Connections</h3><p>This section introduces this library’s basic features and covers some
practical issues with database programming in general and with this
library in particular.</p><h4 x-source-module="(lib "db/scribblings/db.scrbl")" x-source-pkg="db-doc" x-part-tag=""intro-basic"">1.1<tt> </tt><a name="(part._intro-basic)"></a>Introduction to Using Database Connections</h4><p>The following annotated program demonstrates how to connect to a
database and perform simple queries. Some of the SQL syntax used below
is PostgreSQL-specific, such as the syntax of query parameters
(<span class="RktInBG"><span class="hspace"></span><span class="RktIn">$1</span><span class="hspace"></span></span> rather than <span class="RktInBG"><span class="hspace"></span><span class="RktIn">?</span><span class="hspace"></span></span>).</p><blockquote class="SCodeFlow"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="stt">> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=require.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fbase..rkt%2529._require%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">require</a></span><span class="hspace"> </span><span class="RktSym">db</span><span class="RktPn">)</span></td></tr></table></blockquote><p>First we create a connection. Replace <span class="RktVar">user</span>, <span class="RktVar">db</span>,
and <span class="RktVar">password</span> below with the appropriate values for your
configuration (see <a href="connect.html#%28part._creating-connections%29" data-pltdoc="x">Base Connections</a> for other connection examples):</p><blockquote class="SCodeFlow"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="stt">> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=define.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fbase..rkt%2529._define%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">define</a></span><span class="hspace"> </span><span class="RktSym">pgc</span></td></tr><tr><td><span class="hspace"> </span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="connect.html#%28def._%28%28lib._db%2Fmain..rkt%29._postgresql-connect%29%29" class="RktValLink" data-pltdoc="x">postgresql-connect</a></span><span class="hspace"> </span><span class="RktPn">#:user</span><span class="hspace"> </span><span class="RktVar">user</span></td></tr><tr><td><span class="hspace"> </span><span class="hspace"> </span><span class="RktPn">#:database</span><span class="hspace"> </span><span class="RktVar">db</span></td></tr><tr><td><span class="hspace"> </span><span class="hspace"> </span><span class="RktPn">#:password</span><span class="hspace"> </span><span class="RktVar">password</span><span class="RktPn">)</span><span class="RktPn">)</span></td></tr></table></td></tr></table></blockquote><p>Use <span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-exec%29%29" class="RktValLink" data-pltdoc="x">query-exec</a></span> method to execute a SQL statement for effect.</p><blockquote class="SCodeFlow"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="stt">> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-exec%29%29" class="RktValLink" data-pltdoc="x">query-exec</a></span><span class="hspace"> </span><span class="RktSym">pgc</span></td></tr><tr><td><span class="hspace"> </span><span class="hspace"> </span><span class="RktVal">"create temporary table the_numbers (n integer, d varchar(20))"</span><span class="RktPn">)</span></td></tr></table></td></tr><tr><td><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="stt">> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-exec%29%29" class="RktValLink" data-pltdoc="x">query-exec</a></span><span class="hspace"> </span><span class="RktSym">pgc</span></td></tr><tr><td><span class="hspace"> </span><span class="hspace"> </span><span class="RktVal">"insert into the_numbers values (0, 'nothing')"</span><span class="RktPn">)</span></td></tr></table></td></tr><tr><td><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="stt">> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-exec%29%29" class="RktValLink" data-pltdoc="x">query-exec</a></span><span class="hspace"> </span><span class="RktSym">pgc</span></td></tr><tr><td><span class="hspace"> </span><span class="hspace"> </span><span class="RktVal">"insert into the_numbers values (1, 'the loneliest number')"</span><span class="RktPn">)</span></td></tr></table></td></tr></table></blockquote><p>When a query contains a SQL value that isn’t constant, refer to it
through a “query parameter” rather than by dynamically computing the
SQL query string (see also <a href="#%28part._dbsec-sql-injection%29" data-pltdoc="x">SQL Injection</a>). Just
provide the parameter values after the SQL statement in the query
function call:</p><blockquote class="SCodeFlow"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="stt">> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-exec%29%29" class="RktValLink" data-pltdoc="x">query-exec</a></span><span class="hspace"> </span><span class="RktSym">pgc</span></td></tr><tr><td><span class="hspace"> </span><span class="hspace"> </span><span class="RktVal">"insert into the_numbers values ($1, $2)"</span></td></tr><tr><td><span class="hspace"> </span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=generic-numbers.html%23%2528def._%2528%2528quote._%7E23%7E25kernel%2529._%252B%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">+</a></span><span class="hspace"> </span><span class="RktVal">1</span><span class="hspace"> </span><span class="RktVal">1</span><span class="RktPn">)</span></td></tr><tr><td><span class="hspace"> </span><span class="hspace"> </span><span class="RktVal">"company"</span><span class="RktPn">)</span></td></tr></table></td></tr></table></blockquote><p>Every standard query function accepts query parameters. The SQL syntax
for query parameters depends on the database system (see
<a href="query-api.html#%28part._query-statements%29" data-pltdoc="x">Statements</a>). Other options for running parameterized
queries are discussed below.</p><p>The <span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query%29%29" class="RktValLink" data-pltdoc="x">query</a></span> function is a more general way to execute a
statement. It returns a structure encapsulating information about the
statement’s execution. (But some of that information varies from
system to system and is subject to change.)</p><blockquote class="SCodeFlow"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="stt">> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query%29%29" class="RktValLink" data-pltdoc="x">query</a></span><span class="hspace"> </span><span class="RktSym">pgc</span><span class="hspace"> </span><span class="RktVal">"insert into the_numbers values (3, 'a crowd')"</span><span class="RktPn">)</span></td></tr><tr><td><p><span class="RktRes">(simple-result '((insert-id . #f) (affected-rows . 1)))</span></p></td></tr><tr><td><span class="stt">> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query%29%29" class="RktValLink" data-pltdoc="x">query</a></span><span class="hspace"> </span><span class="RktSym">pgc</span><span class="hspace"> </span><span class="RktVal">"select n, d from the_numbers where n % 2 = 0"</span><span class="RktPn">)</span></td></tr><tr><td><table cellspacing="0" cellpadding="0"><tr><td><p><span class="RktRes">(rows-result</span></p></td></tr><tr><td><p><span class="RktRes"></span><span class="hspace"> </span><span class="RktRes">'(((name . "n") (typeid . 23) (type-size . 4) (type-mod . -1))</span></p></td></tr><tr><td><p><span class="RktRes"></span><span class="hspace"> </span><span class="RktRes"></span><span class="hspace"> </span><span class="RktRes">((name . "d") (typeid . 1043) (type-size . -1) (type-mod . 24)))</span></p></td></tr><tr><td><p><span class="RktRes"></span><span class="hspace"> </span><span class="RktRes">'(#(0 "nothing") #(2 "company")))</span></p></td></tr></table></td></tr></table></blockquote><p>When the query is known to return rows and when the field
descriptions are not needed, it is more convenient to use the
<span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-rows%29%29" class="RktValLink" data-pltdoc="x">query-rows</a></span> function.</p><blockquote class="SCodeFlow"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="stt">> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-rows%29%29" class="RktValLink" data-pltdoc="x">query-rows</a></span><span class="hspace"> </span><span class="RktSym">pgc</span><span class="hspace"> </span><span class="RktVal">"select n, d from the_numbers where n % 2 = 0"</span><span class="RktPn">)</span></td></tr><tr><td><p><span class="RktRes">'(#(0 "nothing") #(2 "company"))</span></p></td></tr></table></blockquote><p>Use <span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-row%29%29" class="RktValLink" data-pltdoc="x">query-row</a></span> for queries that are known to return exactly
one row.</p><blockquote class="SCodeFlow"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="stt">> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-row%29%29" class="RktValLink" data-pltdoc="x">query-row</a></span><span class="hspace"> </span><span class="RktSym">pgc</span><span class="hspace"> </span><span class="RktVal">"select * from the_numbers where n = 0"</span><span class="RktPn">)</span></td></tr><tr><td><p><span class="RktRes">'#(0 "nothing")</span></p></td></tr></table></blockquote><p>Similarly, use <span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-list%29%29" class="RktValLink" data-pltdoc="x">query-list</a></span> for queries that produce rows of
exactly one column.</p><blockquote class="SCodeFlow"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="stt">> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-list%29%29" class="RktValLink" data-pltdoc="x">query-list</a></span><span class="hspace"> </span><span class="RktSym">pgc</span><span class="hspace"> </span><span class="RktVal">"select d from the_numbers order by n"</span><span class="RktPn">)</span></td></tr><tr><td><p><span class="RktRes">'("nothing" "the loneliest number" "company" "a crowd")</span></p></td></tr></table></blockquote><p>When a query is known to return a single value (one row and one
column), use <span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-value%29%29" class="RktValLink" data-pltdoc="x">query-value</a></span>.</p><blockquote class="SCodeFlow"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="stt">> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-value%29%29" class="RktValLink" data-pltdoc="x">query-value</a></span><span class="hspace"> </span><span class="RktSym">pgc</span><span class="hspace"> </span><span class="RktVal">"select count(*) from the_numbers"</span><span class="RktPn">)</span></td></tr><tr><td><p><span class="RktRes">4</span></p></td></tr><tr><td><span class="stt">> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-value%29%29" class="RktValLink" data-pltdoc="x">query-value</a></span><span class="hspace"> </span><span class="RktSym">pgc</span><span class="hspace"> </span><span class="RktVal">"select d from the_numbers where n = 5"</span><span class="RktPn">)</span></td></tr><tr><td><p><span class="RktErr">query-value: query returned wrong number of rows</span></p></td></tr><tr><td><p><span class="RktErr"></span><span class="hspace"> </span><span class="RktErr">statement: "select d from the_numbers where n = 5"</span></p></td></tr><tr><td><p><span class="RktErr"></span><span class="hspace"> </span><span class="RktErr">expected: 1</span></p></td></tr><tr><td><p><span class="RktErr"></span><span class="hspace"> </span><span class="RktErr">got: 0</span></p></td></tr></table></blockquote><p>When a query may return zero or one rows, as the last example, use
<span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-maybe-row%29%29" class="RktValLink" data-pltdoc="x">query-maybe-row</a></span> or <span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-maybe-value%29%29" class="RktValLink" data-pltdoc="x">query-maybe-value</a></span> instead.</p><blockquote class="SCodeFlow"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="stt">> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-maybe-value%29%29" class="RktValLink" data-pltdoc="x">query-maybe-value</a></span><span class="hspace"> </span><span class="RktSym">pgc</span><span class="hspace"> </span><span class="RktVal">"select d from the_numbers where n = 5"</span><span class="RktPn">)</span></td></tr><tr><td><p><span class="RktRes">#f</span></p></td></tr></table></blockquote><p>The <span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._in-query%29%29" class="RktValLink" data-pltdoc="x">in-query</a></span> function produces a sequence that can be used
with Racket’s iteration forms:</p><blockquote class="SCodeFlow"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="stt">> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=for.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fbase..rkt%2529._for%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">for</a></span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktPn">[</span><span class="RktPn">(</span><span class="RktSym">n</span><span class="hspace"> </span><span class="RktSym">d</span><span class="RktPn">)</span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._in-query%29%29" class="RktValLink" data-pltdoc="x">in-query</a></span><span class="hspace"> </span><span class="RktSym">pgc</span><span class="hspace"> </span><span class="RktVal">"select * from the_numbers where n < 4"</span><span class="RktPn">)</span><span class="RktPn">]</span><span class="RktPn">)</span></td></tr><tr><td><span class="hspace"> </span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=Writing.html%23%2528def._%2528%2528quote._%7E23%7E25kernel%2529._printf%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">printf</a></span><span class="hspace"> </span><span class="RktVal">"~a: ~a\n"</span><span class="hspace"> </span><span class="RktSym">n</span><span class="hspace"> </span><span class="RktSym">d</span><span class="RktPn">)</span><span class="RktPn">)</span></td></tr></table></td></tr><tr><td><table cellspacing="0" cellpadding="0"><tr><td><p><span class="RktOut">0: nothing</span></p></td></tr><tr><td><p><span class="RktOut">1: the loneliest number</span></p></td></tr><tr><td><p><span class="RktOut">2: company</span></p></td></tr><tr><td><p><span class="RktOut">3: a crowd</span></p></td></tr></table></td></tr><tr><td><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="stt">> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=for.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fbase..rkt%2529._for%252Ffold%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">for/fold</a></span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktPn">[</span><span class="RktSym">sum</span><span class="hspace"> </span><span class="RktVal">0</span><span class="RktPn">]</span><span class="RktPn">)</span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktPn">[</span><span class="RktSym">n</span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._in-query%29%29" class="RktValLink" data-pltdoc="x">in-query</a></span><span class="hspace"> </span><span class="RktSym">pgc</span><span class="hspace"> </span><span class="RktVal">"select n from the_numbers"</span><span class="RktPn">)</span><span class="RktPn">]</span><span class="RktPn">)</span></td></tr><tr><td><span class="hspace"> </span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=generic-numbers.html%23%2528def._%2528%2528quote._%7E23%7E25kernel%2529._%252B%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">+</a></span><span class="hspace"> </span><span class="RktSym">sum</span><span class="hspace"> </span><span class="RktSym">n</span><span class="RktPn">)</span><span class="RktPn">)</span></td></tr></table></td></tr><tr><td><p><span class="RktRes">6</span></p></td></tr></table></blockquote><p>Errors in queries generally do not cause the connection to disconnect.</p><blockquote class="SCodeFlow"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="stt">> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=begin.html%23%2528form._%2528%2528quote._%7E23%7E25kernel%2529._begin%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">begin</a></span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=exns.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fmore-scheme..rkt%2529._with-handlers%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">with-handlers</a></span><span class="hspace"> </span><span class="RktPn">[</span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=exns.html%23%2528def._%2528%2528quote._%7E23%7E25kernel%2529._exn%7E3afail%7E3f%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">exn:fail?</a></span></td></tr><tr><td><span class="hspace"> </span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=lambda.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fbase..rkt%2529._lambda%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">lambda</a></span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym">e</span><span class="RktPn">)</span></td></tr><tr><td><span class="hspace"> </span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=Writing.html%23%2528def._%2528%2528quote._%7E23%7E25kernel%2529._printf%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">printf</a></span><span class="hspace"> </span><span class="RktVal">"~a~n"</span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=exns.html%23%2528def._%2528%2528quote._%7E23%7E25kernel%2529._exn-message%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">exn-message</a></span><span class="hspace"> </span><span class="RktSym">e</span><span class="RktPn">)</span><span class="RktPn">)</span><span class="RktPn">)</span><span class="RktPn">)</span><span class="RktPn">]</span></td></tr><tr><td><span class="hspace"> </span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-value%29%29" class="RktValLink" data-pltdoc="x">query-value</a></span><span class="hspace"> </span><span class="RktSym">pgc</span><span class="hspace"> </span><span class="RktVal">"select NoSuchField from NoSuchTable"</span><span class="RktPn">)</span><span class="RktPn">)</span></td></tr><tr><td><span class="hspace"> </span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-value%29%29" class="RktValLink" data-pltdoc="x">query-value</a></span><span class="hspace"> </span><span class="RktSym">pgc</span><span class="hspace"> </span><span class="RktVal">"select 'okay to proceed!'"</span><span class="RktPn">)</span><span class="RktPn">)</span></td></tr></table></td></tr><tr><td><table cellspacing="0" cellpadding="0"><tr><td><p><span class="RktOut">query-value: relation "nosuchtable" does not exist</span></p></td></tr><tr><td><p><span class="RktOut"></span><span class="hspace"> </span><span class="RktOut">SQLSTATE: 42P01</span></p></td></tr></table></td></tr><tr><td><p><span class="RktRes">"okay to proceed!"</span></p></td></tr></table></blockquote><p>Queries may contain parameters. The easiest way to execute a
parameterized query is to provide the parameters “inline” after the
SQL statement in the query function call.</p><blockquote class="SCodeFlow"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="stt">> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-value%29%29" class="RktValLink" data-pltdoc="x">query-value</a></span><span class="hspace"> </span><span class="RktSym">pgc</span></td></tr><tr><td><span class="hspace"> </span><span class="hspace"> </span><span class="RktVal">"select d from the_numbers where n = $1"</span><span class="hspace"> </span><span class="RktVal">2</span><span class="RktPn">)</span></td></tr></table></td></tr><tr><td><p><span class="RktRes">"company"</span></p></td></tr><tr><td><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="stt">> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-list%29%29" class="RktValLink" data-pltdoc="x">query-list</a></span><span class="hspace"> </span><span class="RktSym">pgc</span></td></tr><tr><td><span class="hspace"> </span><span class="hspace"> </span><span class="RktVal">"select n from the_numbers where n > $1 and n < $2"</span><span class="hspace"> </span><span class="RktVal">0</span><span class="hspace"> </span><span class="RktVal">3</span><span class="RktPn">)</span></td></tr></table></td></tr><tr><td><p><span class="RktRes">'(1 2)</span></p></td></tr></table></blockquote><p>Alternatively, a parameterized query may be prepared in advance and
executed later. <a href="query-api.html#%28tech._prepared._statement%29" class="techoutside" data-pltdoc="x"><span class="techinside">Prepared statements</span></a> can be executed multiple
times with different parameter values.</p><blockquote class="SCodeFlow"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="stt">> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=define.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fbase..rkt%2529._define%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">define</a></span><span class="hspace"> </span><span class="RktSym">get-less-than-pst</span></td></tr><tr><td><span class="hspace"> </span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._prepare%29%29" class="RktValLink" data-pltdoc="x">prepare</a></span><span class="hspace"> </span><span class="RktSym">pgc</span><span class="hspace"> </span><span class="RktVal">"select n from the_numbers where n < $1"</span><span class="RktPn">)</span><span class="RktPn">)</span></td></tr></table></td></tr><tr><td><span class="stt">> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-list%29%29" class="RktValLink" data-pltdoc="x">query-list</a></span><span class="hspace"> </span><span class="RktSym">pgc</span><span class="hspace"> </span><span class="RktSym">get-less-than-pst</span><span class="hspace"> </span><span class="RktVal">1</span><span class="RktPn">)</span></td></tr><tr><td><p><span class="RktRes">'(0)</span></p></td></tr><tr><td><span class="stt">> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-list%29%29" class="RktValLink" data-pltdoc="x">query-list</a></span><span class="hspace"> </span><span class="RktSym">pgc</span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._bind-prepared-statement%29%29" class="RktValLink" data-pltdoc="x">bind-prepared-statement</a></span><span class="hspace"> </span><span class="RktSym">get-less-than-pst</span><span class="hspace"> </span><span class="RktVal">'</span><span class="RktVal">(</span><span class="RktVal">2</span><span class="RktVal">)</span><span class="RktPn">)</span><span class="RktPn">)</span></td></tr><tr><td><p><span class="RktRes">'(0 1)</span></p></td></tr></table></blockquote><p>When a connection’s work is done, it should be disconnected.</p><blockquote class="SCodeFlow"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="stt">> </span><span class="RktPn">(</span><span class="RktSym"><a href="connect.html#%28def._%28%28lib._db%2Fbase..rkt%29._disconnect%29%29" class="RktValLink" data-pltdoc="x">disconnect</a></span><span class="hspace"> </span><span class="RktSym">pgc</span><span class="RktPn">)</span></td></tr></table></blockquote><h4 x-source-module="(lib "db/scribblings/db.scrbl")" x-source-pkg="db-doc" x-part-tag=""dbsec"">1.2<tt> </tt><a name="(part._dbsec)"></a>Database Security</h4><p>Database security requires both that the database back end be secured
against unauthorized use and that authorized clients are not tricked
or subverted into violating the database’s security.</p><p>Securing database back ends is mostly beyond the scope of this
manual. In brief: choose sufficiently strong authentication methods
and keep credentials secure, and follow the
<a href="http://en.wikipedia.org/wiki/Principle_of_least_privilege">principle of least privilege</a>:
create and use roles that have the minimum permissions needed.</p><p>The following is an incomplete list of security issues related to
database <span style="font-style: italic">client</span> programming.</p><h5 x-source-module="(lib "db/scribblings/db.scrbl")" x-source-pkg="db-doc" x-part-tag=""dbsec-sql-injection"">1.2.1<tt> </tt><a name="(part._dbsec-sql-injection)"></a>SQL Injection</h5><p><a href="http://en.wikipedia.org/wiki/SQL_injection">SQL injection</a> happens when part of a SQL
statement that was intended as SQL literal data is instead interpreted
as SQL code—<wbr></wbr>possibly <a href="http://xkcd.com/327/">malicious</a>
SQL code.</p><p>Avoid dynamically creating SQL query strings by string concatenation
or interpolation (eg, with <span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=strings.html%23%2528def._%2528%2528quote._%7E23%7E25kernel%2529._string-append%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">string-append</a></span> or
<span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=Writing.html%23%2528def._%2528%2528quote._%7E23%7E25kernel%2529._format%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">format</a></span>). In most cases, it is possible to use
<a href="query-api.html#%28tech._parameterized._query%29" class="techoutside" data-pltdoc="x"><span class="techinside">parameterized queries</span></a> instead. For example, instead of this</p><p><div class="SIntrapara"><blockquote class="SCodeFlow"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="RktCmt">;</span><span class="RktCmt"> </span><span class="RktCmt">WRONG! DANGER!</span></td></tr><tr><td><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-exec%29%29" class="RktValLink" data-pltdoc="x">query-exec</a></span><span class="hspace"> </span><span class="RktSym">c</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=Writing.html%23%2528def._%2528%2528quote._%7E23%7E25kernel%2529._format%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">format</a></span><span class="hspace"> </span><span class="RktVal">"UPDATE users SET passwd='~a' WHERE user='~a'"</span></td></tr><tr><td><span class="hspace"> </span><span class="RktSym">user</span><span class="hspace"> </span><span class="RktSym">new-passwd</span><span class="RktPn">)</span><span class="RktPn">)</span></td></tr></table></blockquote></div><div class="SIntrapara">write one of the following instead (depending on SQL dialect):
</div><div class="SIntrapara"><blockquote class="SCodeFlow"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="RktCmt">;</span><span class="RktCmt"> </span><span class="RktCmt">for PostgreSQL, SQLite</span></td></tr><tr><td><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-exec%29%29" class="RktValLink" data-pltdoc="x">query-exec</a></span><span class="hspace"> </span><span class="RktSym">c</span><span class="hspace"> </span><span class="RktVal">"UPDATE users SET passwd=$1 WHERE user=$2"</span><span class="hspace"> </span><span class="RktSym">user</span><span class="hspace"> </span><span class="RktSym">new-passwd</span><span class="RktPn">)</span></td></tr><tr><td><span class="RktCmt">;</span><span class="RktCmt"> </span><span class="RktCmt">for MySQL, SQLite, ODBC</span></td></tr><tr><td><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-exec%29%29" class="RktValLink" data-pltdoc="x">query-exec</a></span><span class="hspace"> </span><span class="RktSym">c</span><span class="hspace"> </span><span class="RktVal">"UPDATE users SET passwd=? WHERE user=?"</span><span class="hspace"> </span><span class="RktSym">user</span><span class="hspace"> </span><span class="RktSym">new-passwd</span><span class="RktPn">)</span></td></tr></table></blockquote></div></p><p>The first form would choke on names like <span class="RktVal">"Patrick O'Connor"</span>.
Worse, it would be susceptible to attack by malicious input like
<span class="RktVal">"me' OR user='root'"</span>, which yields the following SQL
statement:</p><p><span class="stt">UPDATE users SET passwd='whatever' WHERE user='me' OR user='root'</span></p><p>In contrast, using a <a href="query-api.html#%28tech._parameterized._query%29" class="techoutside" data-pltdoc="x"><span class="techinside">parameterized query</span></a> causes the
parameterized SQL and its arguments to be submitted to the back end
separately; the back end then combines them safely.</p><p>Only SQL literal values can be replaced with parameter placeholders; a
SQL statement cannot be parameterized over a column name or a sort
order, for example. In such cases, constructing the query dynamically
may be the only feasible solution. But while the query construction
may be influenced by external input, it should never directly
incorporate external input without validation. That is, don’t do the
following:</p><blockquote class="SCodeFlow"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="RktCmt">;</span><span class="RktCmt"> </span><span class="RktCmt">WRONG! DANGER!</span></td></tr><tr><td><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-rows%29%29" class="RktValLink" data-pltdoc="x">query-rows</a></span><span class="hspace"> </span><span class="RktSym">c</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=Writing.html%23%2528def._%2528%2528quote._%7E23%7E25kernel%2529._format%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">format</a></span><span class="hspace"> </span><span class="RktVal">"SELECT name, ~a FROM contestants"</span><span class="hspace"> </span><span class="RktSym">column</span><span class="RktPn">)</span><span class="RktPn">)</span></td></tr><tr><td><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-list%29%29" class="RktValLink" data-pltdoc="x">query-list</a></span><span class="hspace"> </span><span class="RktSym">c</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=Writing.html%23%2528def._%2528%2528quote._%7E23%7E25kernel%2529._format%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">format</a></span><span class="hspace"> </span><span class="RktVal">"SELECT name FROM contestants ORDER BY score ~a"</span><span class="hspace"> </span><span class="RktSym">direction</span><span class="RktPn">)</span><span class="RktPn">)</span></td></tr></table></blockquote><p>Instead, select the inserted SQL from known good alternatives:</p><blockquote class="SCodeFlow"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="RktCmt">;</span><span class="RktCmt"> </span><span class="RktCmt">BETTER</span></td></tr><tr><td><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-rows%29%29" class="RktValLink" data-pltdoc="x">query-rows</a></span><span class="hspace"> </span><span class="RktSym">c</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=Writing.html%23%2528def._%2528%2528quote._%7E23%7E25kernel%2529._format%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">format</a></span><span class="hspace"> </span><span class="RktVal">"SELECT name, ~a FROM contestants"</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=if.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fletstx-scheme..rkt%2529._cond%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">cond</a></span><span class="hspace"> </span><span class="RktPn">[</span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=pairs.html%23%2528def._%2528%2528lib._racket%252Fprivate%252Fbase..rkt%2529._member%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">member</a></span><span class="hspace"> </span><span class="RktSym">column</span><span class="hspace"> </span><span class="RktVal">'</span><span class="RktVal">(</span><span class="RktVal">"wins"</span><span class="hspace"> </span><span class="RktVal">"losses"</span><span class="RktVal">)</span><span class="RktPn">)</span><span class="hspace"> </span><span class="RktSym">column</span><span class="RktPn">]</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">[</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=if.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fletstx-scheme..rkt%2529._else%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">else</a></span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=exns.html%23%2528def._%2528%2528quote._%7E23%7E25kernel%2529._error%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">error</a></span><span class="hspace"> </span><span class="RktSym">....</span><span class="RktPn">)</span><span class="RktPn">]</span><span class="RktPn">)</span><span class="RktPn">)</span><span class="RktPn">)</span></td></tr><tr><td><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-list%29%29" class="RktValLink" data-pltdoc="x">query-list</a></span><span class="hspace"> </span><span class="RktSym">c</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=Writing.html%23%2528def._%2528%2528quote._%7E23%7E25kernel%2529._format%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">format</a></span><span class="hspace"> </span><span class="RktVal">"SELECT name FROM contestants ORDER BY score ~a"</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=if.html%23%2528form._%2528%2528quote._%7E23%7E25kernel%2529._if%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">if</a></span><span class="hspace"> </span><span class="RktSym">ascending?</span><span class="hspace"> </span><span class="RktVal">"ASC"</span><span class="hspace"> </span><span class="RktVal">"DESC"</span><span class="RktPn">)</span><span class="RktPn">)</span><span class="RktPn">)</span></td></tr></table></blockquote><h5 x-source-module="(lib "db/scribblings/db.scrbl")" x-source-pkg="db-doc" x-part-tag=""dbsec-xss"">1.2.2<tt> </tt><a name="(part._dbsec-xss)"></a>Cross-site Scripting (XSS)</h5><p><a href="http://en.wikipedia.org/wiki/Cross-site_scripting">Cross-site scripting</a>—<wbr></wbr>which should
probably be called “HTML injection” or “markup injection”—<wbr></wbr>is
when arbitrary text from an untrusted source is embedded without
escaping into an HTML page. The <span style="font-style: italic">unstructured text from the
untrusted source</span> is reinterpreted as <span style="font-style: italic">markup from the web
server</span>; if the reinterpreted markup contains embedded Javascript
code, it executes with the security privileges associated with the web
server’s domain.</p><p>This issue has little to do with databases <span style="font-style: italic">per se</span> except that
such text is often stored in a database. This issue is mitigated by
using structured markup representations like SXML or X-expressions
(xexprs), since they automatically escape “markup” characters found
in embedded text.</p><h5 x-source-module="(lib "db/scribblings/db.scrbl")" x-source-pkg="db-doc" x-part-tag=""dbsec-connect"">1.2.3<tt> </tt><a name="(part._dbsec-connect)"></a>Making Database Connections Securely</h5><p>When connecting to a database server over a network, use TLS/SSL and take the
steps necessary to use it securely. Without TLS, your data and connection
credentials are exposed to any attackers with access to the network. The
password-hashing schemes used by PostgreSQL and MySQL are
<a href="https://codahale.com/how-to-safely-store-a-password/">insufficient</a> to
<a href="http://security.stackexchange.com/questions/211/how-to-securely-hash-passwords">protect</a>
short or common passwords.</p><p>Both <span class="RktSym"><a href="connect.html#%28def._%28%28lib._db%2Fmain..rkt%29._postgresql-connect%29%29" class="RktValLink" data-pltdoc="x">postgresql-connect</a></span> and <span class="RktSym"><a href="connect.html#%28def._%28%28lib._db%2Fmain..rkt%29._mysql-connect%29%29" class="RktValLink" data-pltdoc="x">mysql-connect</a></span> support TLS; connect
with <span class="RktPn">#:ssl</span><span class="stt"> </span><span class="RktVal">'</span><span class="RktVal">yes</span> and <span class="RktPn">#:ssl-context</span><span class="stt"> </span><span class="RktVar">secure-context</span>, where</p><ul><li><p>If the server has a certificate issued by a well-known, trusted
certificate authority (CA), you can probably just use
<span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=openssl&rel=index.html%23%2528def._%2528%2528lib._openssl%252Fmain..rkt%2529._ssl-secure-client-context%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">ssl-secure-client-context</a></span><span class="RktPn">)</span> with the default verification sources
managed by your operating system.</p></li><li><p>Otherwise, you must create a context that trusts your server’s
certificate. Obtain the server’s certificate or the certificate of its CA as a
PEM file; suppose the file is located at <span class="RktVal">"/path/to/server-cert.pem"</span>.
Create the context as follows:</p><blockquote class="SCodeFlow"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=parameters.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fmore-scheme..rkt%2529._parameterize%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">parameterize</a></span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=openssl&rel=index.html%23%2528def._%2528%2528lib._openssl%252Fmain..rkt%2529._ssl-default-verify-sources%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">ssl-default-verify-sources</a></span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=pairs.html%23%2528def._%2528%2528quote._%7E23%7E25kernel%2529._list%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">list</a></span><span class="hspace"> </span><span class="RktVal">"/path/to/server-cert.pem"</span><span class="RktPn">)</span><span class="RktPn">)</span><span class="RktPn">)</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=openssl&rel=index.html%23%2528def._%2528%2528lib._openssl%252Fmain..rkt%2529._ssl-secure-client-context%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">ssl-secure-client-context</a></span><span class="RktPn">)</span><span class="RktPn">)</span></td></tr></table></blockquote><p>See also <span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=openssl&rel=index.html%23%2528def._%2528%2528lib._openssl%252Fmain..rkt%2529._ssl-load-verify-source%2521%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">ssl-load-verify-source!</a></span> for other kinds of verification
sources.</p></li></ul><p>For ODBC connections, as always, consult the back end and ODBC driver
documentation.</p><h4 x-source-module="(lib "db/scribblings/db.scrbl")" x-source-pkg="db-doc" x-part-tag=""dbperf"">1.3<tt> </tt><a name="(part._dbperf)"></a>Database Performance</h4><p>Achieving good database performance mostly consists of good database
design and intelligent client behavior.</p><p>On the database design side, most important are wise use of indexes
and choosing appropriate data representations. As an example of the
latter, a regexp-based search using <span class="stt">LIKE</span> will probably be slower
than a specialized
<a href="http://www.postgresql.org/docs/9.0/static/textsearch.html">full-text
search</a> feature for large data sets. Consult your database back end’s
manual for additional performance advice.</p><p>The following sections describe a few client-side aspects of
performance.</p><h5 x-source-module="(lib "db/scribblings/db.scrbl")" x-source-pkg="db-doc" x-part-tag=""dbperf-n+1"">1.3.1<tt> </tt><a name="(part._dbperf-n+1)"></a>The N+1 Selects Problem</h5><p>A common mistake is to fetch a large amount of data by running a query
to get a set of initial records and then running another query inside
a loop with an iteration for each of the initial records. This is
sometimes called the “n+1 selects problem.” For example:</p><blockquote class="SCodeFlow"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=for.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fbase..rkt%2529._for%252Flist%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">for/list</a></span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktPn">[</span><span class="RktPn">(</span><span class="RktSym">name</span><span class="hspace"> </span><span class="RktSym">id</span><span class="RktPn">)</span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._in-query%29%29" class="RktValLink" data-pltdoc="x">in-query</a></span><span class="hspace"> </span><span class="RktSym">c</span><span class="hspace"> </span><span class="RktVal">"SELECT name, id FROM contestants"</span><span class="RktPn">)</span><span class="RktPn">]</span><span class="RktPn">)</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=define.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fbase..rkt%2529._define%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">define</a></span><span class="hspace"> </span><span class="RktSym">wins</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query-list%29%29" class="RktValLink" data-pltdoc="x">query-list</a></span><span class="hspace"> </span><span class="RktSym">c</span><span class="hspace"> </span><span class="RktVal">"SELECT contest FROM contests WHERE winner = $1"</span><span class="hspace"> </span><span class="RktSym">id</span><span class="RktPn">)</span><span class="RktPn">)</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym">make-contestant-record</span><span class="hspace"> </span><span class="RktSym">name</span><span class="hspace"> </span><span class="RktSym">wins</span><span class="RktPn">)</span><span class="RktPn">)</span></td></tr></table></blockquote><p>The same information can be retrieved in a single query by performing
a <span class="stt">LEFT OUTER JOIN</span> and grouping the results:</p><blockquote class="SCodeFlow"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=for.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fbase..rkt%2529._for%252Flist%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">for/list</a></span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktPn">[</span><span class="RktPn">(</span><span class="RktSym">name</span><span class="hspace"> </span><span class="RktSym">id</span><span class="hspace"> </span><span class="RktSym">wins</span><span class="RktPn">)</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._in-query%29%29" class="RktValLink" data-pltdoc="x">in-query</a></span><span class="hspace"> </span><span class="RktSym">c</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=strings.html%23%2528def._%2528%2528quote._%7E23%7E25kernel%2529._string-append%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">string-append</a></span><span class="hspace"> </span><span class="RktVal">"SELECT name, id, contest "</span></td></tr><tr><td><span class="hspace"> </span><span class="RktVal">"FROM contestants LEFT OUTER JOIN contests "</span></td></tr><tr><td><span class="hspace"> </span><span class="RktVal">"ON contestants.id = contests.winner"</span><span class="RktPn">)</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">#:group</span><span class="hspace"> </span><span class="RktVal">'</span><span class="RktVal">(</span><span class="RktVal">#</span><span class="RktVal">(</span><span class="RktVal">"name"</span><span class="hspace"> </span><span class="RktVal">"id"</span><span class="RktVal">)</span><span class="RktVal">)</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">#:group-mode</span><span class="hspace"> </span><span class="RktVal">'</span><span class="RktVal">(</span><span class="RktVal">list</span><span class="RktVal">)</span><span class="RktPn">)</span><span class="RktPn">]</span><span class="RktPn">)</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym">make-contestant-record</span><span class="hspace"> </span><span class="RktSym">name</span><span class="hspace"> </span><span class="RktSym">wins</span><span class="RktPn">)</span><span class="RktPn">)</span></td></tr></table></blockquote><p>The one-query form will perform better when database communication has
high latency. On the other hand, it may duplicate the contents of the
non-key <span class="stt">name</span> column, using more bandwidth. Another approach is to
perform two queries:</p><blockquote class="SCodeFlow"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=let.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fletstx-scheme..rkt%2529._let%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">let</a></span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktPn">[</span><span class="RktSym">id=>name</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._rows-~3edict%29%29" class="RktValLink" data-pltdoc="x">rows->dict</a></span><span class="hspace"> </span><span class="RktPn">#:key</span><span class="hspace"> </span><span class="RktVal">"id"</span><span class="hspace"> </span><span class="RktPn">#:value</span><span class="hspace"> </span><span class="RktVal">"name"</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._query%29%29" class="RktValLink" data-pltdoc="x">query</a></span><span class="hspace"> </span><span class="RktSym">c</span><span class="hspace"> </span><span class="RktVal">"SELECT id, name FROM contestants"</span><span class="RktPn">)</span><span class="RktPn">)</span><span class="RktPn">]</span><span class="RktPn">)</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=for.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fbase..rkt%2529._for%252Flist%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">for/list</a></span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktPn">[</span><span class="RktPn">(</span><span class="RktSym">id</span><span class="hspace"> </span><span class="RktSym">wins</span><span class="RktPn">)</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._in-query%29%29" class="RktValLink" data-pltdoc="x">in-query</a></span><span class="hspace"> </span><span class="RktSym">c</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=strings.html%23%2528def._%2528%2528quote._%7E23%7E25kernel%2529._string-append%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">string-append</a></span><span class="hspace"> </span><span class="RktVal">"SELECT id, contest "</span></td></tr><tr><td><span class="hspace"> </span><span class="RktVal">"FROM contestants LEFT OUTER JOIN contests "</span></td></tr><tr><td><span class="hspace"> </span><span class="RktVal">"ON contestants.id = contests.winner"</span><span class="RktPn">)</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">#:group</span><span class="hspace"> </span><span class="RktVal">'</span><span class="RktVal">(</span><span class="RktVal">#</span><span class="RktVal">(</span><span class="RktVal">"id"</span><span class="RktVal">)</span><span class="RktVal">)</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">#:group-mode</span><span class="hspace"> </span><span class="RktVal">'</span><span class="RktVal">(</span><span class="RktVal">list</span><span class="RktVal">)</span><span class="RktPn">)</span><span class="RktPn">]</span><span class="RktPn">)</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym">make-contestant-record</span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=dicts.html%23%2528def._%2528%2528lib._racket%252Fdict..rkt%2529._dict-ref%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">dict-ref</a></span><span class="hspace"> </span><span class="RktSym">id=>name</span><span class="hspace"> </span><span class="RktSym">id</span><span class="RktPn">)</span><span class="hspace"> </span><span class="RktSym">wins</span><span class="RktPn">)</span><span class="RktPn">)</span><span class="RktPn">)</span></td></tr></table></blockquote><p>Compared with the one-query form, the two-query form requires
additional communication, but it avoids duplicating <span class="stt">name</span> values
in the <span class="stt">OUTER JOIN</span> results. If additional non-key <span class="stt">contestant</span>
fields were to be retrieved, the bandwidth savings of this approach
would be even greater.</p><p>See also <a href="#%28part._dbperf-testing%29" data-pltdoc="x">Testing Performance of Database-Backed Programs</a>.</p><h5 x-source-module="(lib "db/scribblings/db.scrbl")" x-source-pkg="db-doc" x-part-tag=""dbperf-update-tx"">1.3.2<tt> </tt><a name="(part._dbperf-update-tx)"></a>Updates and Transactions</h5><p>Using transactions can dramatically improve the performance of bulk
database operations, especially <span class="stt">UPDATE</span> and <span class="stt">INSERT</span>
statements. As an extreme example, on commodity hardware in 2012,
SQLite is capable of executing thousands of <span class="stt">INSERT</span> statements per
second within a transaction, but it is capable of only dozens of
single-<span class="stt">INSERT</span> transactions per second.</p><h5 x-source-module="(lib "db/scribblings/db.scrbl")" x-source-pkg="db-doc" x-part-tag=""dbperf-pstcache"">1.3.3<tt> </tt><a name="(part._dbperf-pstcache)"></a>Statement Caching</h5><p>Connections cache implicitly prepared statements (that is, statements
given in string form directly to a query function). The effect of the
cache is to eliminate an extra round-trip to the server (to send the
statement and receive a prepared statement handle), leaving just a
single round-trip (to send parameters and receive results) per
execution.</p><p>Currently, prepared statements are only cached within a
transaction. The statement cache is flushed when entering or leaving a
transaction and whenever a DDL statement is executed.</p><h5 x-source-module="(lib "db/scribblings/db.scrbl")" x-source-pkg="db-doc" x-part-tag=""dbperf-testing"">1.3.4<tt> </tt><a name="(part._dbperf-testing)"></a>Testing Performance of Database-Backed Programs</h5><p>When testing the performance of database-backed programs, remember to
test them in environments with realistic latency and
bandwidth. High-latency environments may be roughly approximated with
the <span class="RktSym"><a href="util.html#%28def._%28%28lib._db%2Futil%2Ftesting..rkt%29._high-latency-connection%29%29" class="RktValLink" data-pltdoc="x">high-latency-connection</a></span> function, but there’s no
substitute for the real thing.</p><h5 x-source-module="(lib "db/scribblings/db.scrbl")" x-source-pkg="db-doc" x-part-tag=""dbperf-concurrency"">1.3.5<tt> </tt><a name="(part._dbperf-concurrency)"></a>Transactions and Concurrency</h5><p>Database systems use
<a href="http://en.wikipedia.org/wiki/Database_transaction">transactions</a>
to guarantee properties such as
<a href="http://en.wikipedia.org/wiki/ACID">atomicity and
isolation</a> while accommodating concurrent reads and writes by the
database’s clients. Within a transaction a client is insulated from
the actions of other clients, but the transaction may be aborted and
rolled back if the database system cannot reconcile it with other
concurrent interactions. Some database systems are more adept at
reconciling transactions than others, and most allow reconciliation to
be tuned through the specification of <span style="font-style: italic">isolation levels</span>.</p><p>PostgreSQL supports
<a href="http://www.postgresql.org/docs/9.2/static/mvcc.html">very
fine-grained reconciliation</a>: two transactions that both read and
modify the same table concurrently might both be allowed to complete
if they involve disjoint sets of rows. However, clients should be
prepared to retry transactions that fail with a <span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._exn~3afail~3asql%29%29" class="RktValLink" data-pltdoc="x">exn:fail:sql</a></span>
exception with SQLSTATE matching <span class="RktVal">#rx"^40...$"</span>—<wbr></wbr>typically
<span class="RktVal">"40001"</span>, “could not serialize access due to concurrent
update.”</p><p>MySQL’s transaction behavior varies based on the storage drivers in
use. Clients should be prepared to retry transactions that fail with a
<span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._exn~3afail~3asql%29%29" class="RktValLink" data-pltdoc="x">exn:fail:sql</a></span> exception with SQLSTATE matching
<span class="RktVal">#rx"^40...$"</span>.</p><p>SQLite enforces a
<a href="http://www.sqlite.org/lockingv3.html">very coarse-grained
policy</a>: only one transaction is allowed to write to the database at a
time, and thus concurrent writers are very likely to conflict. Clients
should be prepared to retry transactions that fail with a
<span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._exn~3afail~3asql%29%29" class="RktValLink" data-pltdoc="x">exn:fail:sql</a></span> exception with SQLSTATE of <span class="RktVal">'</span><span class="RktVal">busy</span>.</p><p>An alternative to retrying whole SQLite transactions is to start each
transaction with the appropriate locking level, since a transaction
usually fails when it is unable to upgrade its lock level. Start a
transaction that only performs reads in the default mode, and start a
transaction that may perform writes in <span class="RktVal">'</span><span class="RktVal">immediate</span> mode (see
<span class="RktSym"><a href="query-api.html#%28def._%28%28lib._db%2Fbase..rkt%29._start-transaction%29%29" class="RktValLink" data-pltdoc="x">start-transaction</a></span>). That converts the problem of retrying
whole transactions into the problem of retrying the initial <span class="stt">BEGIN
TRANSACTION</span> statment, and this library already automatically retries
individual statements that fail with <span class="RktVal">'</span><span class="RktVal">busy</span> errors. Depending
on the length and frequency of the transactions, you may need to
adjust <span class="RktVar">busy-retry-limit</span> (see <span class="RktSym"><a href="connect.html#%28def._%28%28lib._db%2Fmain..rkt%29._sqlite3-connect%29%29" class="RktValLink" data-pltdoc="x">sqlite3-connect</a></span>).</p><p>ODBC’s behavior varies depending on the driver and back end. See the
appropriate database system’s documentation.</p><h4 x-source-module="(lib "db/scribblings/db.scrbl")" x-source-pkg="db-doc" x-part-tag=""intro-servlets"">1.4<tt> </tt><a name="(part._intro-servlets)"></a>Databases and Web Servlets</h4><p>Using database connections in a web servlet is more complicated than
in a standalone program. A single servlet potentially serves many
requests at once, each in a separate request-handling
thread. Furthermore, the use of <span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=web-server&rel=stateless.html%23%2528def._%2528%2528lib._web-server%252Flang%252Fweb..rkt%2529._send%252Fsuspend%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">send/suspend</a></span>,
<span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=web-server&rel=stateless.html%23%2528def._%2528%2528lib._web-server%252Flang%252Fweb..rkt%2529._send%252Fsuspend%252Fdispatch%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">send/suspend/dispatch</a></span>, etc means that there are many places
where a servlet may start and stop executing to service a request.</p><p>Why not use a single connection to handle all of a servlet’s requests?
That is, create the connection with the servlet instance and never
disconnect it. Such a servlet would look something like the following:</p><blockquote class="SCodeFlow"><blockquote class="Rfilebox"><p class="Rfiletitle"><span class="Rfilename"><span class="stt">"bad-servlet.rkt"</span></span></p><blockquote class="Rfilecontent"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=guide&rel=Module_Syntax.html%23%2528part._hash-lang%2529&version=6.7" class="RktModLink Sq" data-pltdoc="x"><span class="RktMod">#lang</span></a><span class="hspace"> </span><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=web-server&rel=stateless.html&version=6.7" class="RktModLink Sq" data-pltdoc="x"><span class="RktSym">web-server</span></a></td></tr><tr><td><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=require.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fbase..rkt%2529._require%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">require</a></span><span class="hspace"> </span><span class="RktSym">db</span><span class="RktPn">)</span></td></tr><tr><td><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=define.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fbase..rkt%2529._define%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">define</a></span><span class="hspace"> </span><span class="RktSym">db-conn</span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="connect.html#%28def._%28%28lib._db%2Fmain..rkt%29._postgresql-connect%29%29" class="RktValLink" data-pltdoc="x">postgresql-connect</a></span><span class="hspace"> </span><span class="RktSym">....</span><span class="RktPn">)</span><span class="RktPn">)</span></td></tr><tr><td><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=define.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fbase..rkt%2529._define%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">define</a></span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym">serve</span><span class="hspace"> </span><span class="RktSym">req</span><span class="RktPn">)</span></td></tr><tr><td><span class="hspace"> </span><span class="RktSym">....</span><span class="hspace"> </span><span class="RktSym">db-conn</span><span class="hspace"> </span><span class="RktSym">....</span><span class="RktPn">)</span></td></tr></table></blockquote></blockquote></blockquote><p>The main problem with using one connection for all requests is that
multiple threads accessing the same connection are not properly
<a href="http://en.wikipedia.org/wiki/Isolation_%28database_systems%29">isolated</a>. For example, if
one thread is accessing the connection within a transaction and
another thread issues a query, the second thread may see invalid data
or even disrupt the work of the first thread.</p><p>A secondary problem is performance. A connection can only perform a
single query at a time, whereas most database systems are capable of
concurrent query processing.</p><p>The proper way to use database connections in a servlet is to create a
connection for each request and disconnect it when the request has
been handled. But since a request thread may start and stop executing
in many places (due to <span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=web-server&rel=stateless.html%23%2528def._%2528%2528lib._web-server%252Flang%252Fweb..rkt%2529._send%252Fsuspend%2529%2529&version=6.7" class="RktValLink Sq" data-pltdoc="x">send/suspend</a></span>, etc), inserting the code
to connect and disconnect at the proper places can be challenging and
messy.</p><p>A better solution is to use a <a href="connect.html#%28tech._virtual._connection%29" class="techoutside" data-pltdoc="x"><span class="techinside">virtual connection</span></a>, which
automatically creates a request-specific (that is, thread-specific)
“actual connection” by need and disconnects it when the request has
been handled (that is, when the thread terminates). Different
request-handling threads using the same virtual connection are
assigned different actual connections, so the requests are properly
isolated.</p><blockquote class="SCodeFlow"><blockquote class="Rfilebox"><p class="Rfiletitle"><span class="Rfilename"><span class="stt">"better-servlet.rkt"</span></span></p><blockquote class="Rfilecontent"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=guide&rel=Module_Syntax.html%23%2528part._hash-lang%2529&version=6.7" class="RktModLink Sq" data-pltdoc="x"><span class="RktMod">#lang</span></a><span class="hspace"> </span><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=web-server&rel=stateless.html&version=6.7" class="RktModLink Sq" data-pltdoc="x"><span class="RktSym">web-server</span></a></td></tr><tr><td><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=require.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fbase..rkt%2529._require%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">require</a></span><span class="hspace"> </span><span class="RktSym">db</span><span class="RktPn">)</span></td></tr><tr><td><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=define.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fbase..rkt%2529._define%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">define</a></span><span class="hspace"> </span><span class="RktSym">db-conn</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="connect.html#%28def._%28%28lib._db%2Fbase..rkt%29._virtual-connection%29%29" class="RktValLink" data-pltdoc="x">virtual-connection</a></span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=lambda.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fbase..rkt%2529._lambda%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">lambda</a></span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktPn">)</span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="connect.html#%28def._%28%28lib._db%2Fmain..rkt%29._postgresql-connect%29%29" class="RktValLink" data-pltdoc="x">postgresql-connect</a></span><span class="hspace"> </span><span class="RktSym">....</span><span class="RktPn">)</span><span class="RktPn">)</span><span class="RktPn">)</span><span class="RktPn">)</span></td></tr><tr><td><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=define.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fbase..rkt%2529._define%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">define</a></span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym">serve</span><span class="hspace"> </span><span class="RktSym">req</span><span class="RktPn">)</span></td></tr><tr><td><span class="hspace"> </span><span class="RktSym">....</span><span class="hspace"> </span><span class="RktSym">db-conn</span><span class="hspace"> </span><span class="RktSym">....</span><span class="RktPn">)</span></td></tr></table></blockquote></blockquote></blockquote><p>This solution preserves the simplicity of the naive solution and fixes
the isolation problem but at the cost of creating many short-lived
database connections. That cost can be eliminated by using a
<a href="connect.html#%28tech._connection._pool%29" class="techoutside" data-pltdoc="x"><span class="techinside">connection pool</span></a>:</p><blockquote class="SCodeFlow"><blockquote class="Rfilebox"><p class="Rfiletitle"><span class="Rfilename"><span class="stt">"best-servlet.rkt"</span></span></p><blockquote class="Rfilecontent"><table cellspacing="0" cellpadding="0" class="RktBlk"><tr><td><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=guide&rel=Module_Syntax.html%23%2528part._hash-lang%2529&version=6.7" class="RktModLink Sq" data-pltdoc="x"><span class="RktMod">#lang</span></a><span class="hspace"> </span><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=web-server&rel=stateless.html&version=6.7" class="RktModLink Sq" data-pltdoc="x"><span class="RktSym">web-server</span></a></td></tr><tr><td><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=require.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fbase..rkt%2529._require%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">require</a></span><span class="hspace"> </span><span class="RktSym">db</span><span class="RktPn">)</span></td></tr><tr><td><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=define.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fbase..rkt%2529._define%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">define</a></span><span class="hspace"> </span><span class="RktSym">db-conn</span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="connect.html#%28def._%28%28lib._db%2Fbase..rkt%29._virtual-connection%29%29" class="RktValLink" data-pltdoc="x">virtual-connection</a></span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="connect.html#%28def._%28%28lib._db%2Fbase..rkt%29._connection-pool%29%29" class="RktValLink" data-pltdoc="x">connection-pool</a></span></td></tr><tr><td><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=lambda.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fbase..rkt%2529._lambda%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">lambda</a></span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktPn">)</span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym"><a href="connect.html#%28def._%28%28lib._db%2Fmain..rkt%29._postgresql-connect%29%29" class="RktValLink" data-pltdoc="x">postgresql-connect</a></span><span class="hspace"> </span><span class="RktSym">....</span><span class="RktPn">)</span><span class="RktPn">)</span><span class="RktPn">)</span><span class="RktPn">)</span><span class="RktPn">)</span></td></tr><tr><td><span class="RktPn">(</span><span class="RktSym"><a href="https://download.racket-lang.org/docs/6.7/html/local-redirect/index.html?doc=reference&rel=define.html%23%2528form._%2528%2528lib._racket%252Fprivate%252Fbase..rkt%2529._define%2529%2529&version=6.7" class="RktStxLink Sq" data-pltdoc="x">define</a></span><span class="hspace"> </span><span class="RktPn">(</span><span class="RktSym">serve</span><span class="hspace"> </span><span class="RktSym">req</span><span class="RktPn">)</span></td></tr><tr><td><span class="hspace"> </span><span class="RktSym">....</span><span class="hspace"> </span><span class="RktSym">db-conn</span><span class="hspace"> </span><span class="RktSym">....</span><span class="RktPn">)</span></td></tr></table></blockquote></blockquote></blockquote><p>By using a virtual connection backed by a connection pool, a servlet
can achieve simplicity, isolation, and performance all at the same
time.</p><div class="navsetbottom"><span class="navleft"><form class="searchform"><input class="searchbox" style="color: #888;" type="text" value="...search manuals..." title="Enter a search string to search the manuals" onkeypress="return DoSearchKey(event, this, "6.7", "../");" onfocus="this.style.color="black"; this.style.textAlign="left"; if (this.value == "...search manuals...") this.value="";" onblur="if (this.value.match(/^ *$/)) { this.style.color="#888"; this.style.textAlign="center"; this.value="...search manuals..."; }"/></form> <a href="../index.html" title="up to the documentation top" data-pltdoc="x" onclick="return GotoPLTRoot("6.7");">top</a></span><span class="navright"> <a href="index.html" title="backward to "DB: Database Connectivity"" data-pltdoc="x">← prev</a> <a href="index.html" title="up to "DB: Database Connectivity"" data-pltdoc="x">up</a> <a href="connect.html" title="forward to "2 Connections"" data-pltdoc="x">next →</a></span> </div></div></div><div id="contextindicator"> </div></body></html>
|