Debian Codegraph @

www/nulinstr.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>NUL Characters In Strings</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 NUL Characters In Strings
122 </div>
123 </div>
124
125
126
127
128
129 <h1 id="introduction"><span>1. </span>Introduction</h1>
130
131 <p>
132 SQLite allows NUL characters (ASCII 0x00, Unicode \u0000) in the middle
133 of string values stored in the database. However, the use of NUL within
134 strings can lead to surprising behaviors:
135
136 </p><ol>
137 <li><p>
138 The <a href="lang_corefunc.html#length">length() SQL function</a> only counts characters up to and excluding
139 the first NUL.
140
141
142 </p></li><li><p>
143 The <a href="lang_corefunc.html#quote">quote() SQL function</a> only shows characters up to and excluding
144 the first NUL.
145
146 </p></li><li><p>
147 The <a href="cli.html#dump">.dump</a> command in the <a href="cli.html">CLI</a> omits the first NUL character and all
148 subsequent text in the SQL output that it generates. In fact, the
149 <a href="cli.html">CLI</a> omits everything past the first NUL character in all contexts.
150 </p></li></ol>
151
152 <p>
153 The use of NUL characters in SQL text strings is not recommended.
154
155 </p><h1 id="unexpected_behavior"><span>2. </span>Unexpected Behavior</h1>
156
157 <p>
158 Consider the following SQL:
159
160 </p><div class="codeblock"><pre>CREATE TABLE t1(
161 a INTEGER PRIMARY KEY,
162 b TEXT
163 );
164 INSERT INTO t1(a,b) VALUES(1, 'abc'||char(0)||'xyz');
165
166 SELECT a, b, length(b) FROM t1;
167 </pre></div>
168
169 <p>
170 The SELECT statement above shows output of:
171
172 </p><div class="codeblock"><pre>1,'abc',3
173 </pre></div>
174
175 <p>
176 (Through this document, we assume that the <a href="cli.html">CLI</a> has "<a href="climode.html#dotmodequote">.mode quote</a>" set.)
177 But if you run:
178
179 </p><div class="codeblock"><pre>SELECT * FROM t1 WHERE b='abc';
180 </pre></div>
181
182 <p>
183 Then no rows are returned. SQLite knows that the t1.b column actually
184 holds a 7-character string, and the 7-character string 'abc'||char(0)||'xyz'
185 is not equal to the 3-character string 'abc', and so no rows are returned.
186 But a user might be easily confused by this because the <a href="cli.html">CLI</a> output
187 seems to show that the string has only 3 characters. This seems like
188 a bug. But it is how SQLite works.
189
190 </p><h1 id="how_to_tell_if_you_have_nul_characters_in_your_strings"><span>3. </span>How To Tell If You Have NUL Characters In Your Strings</h1>
191
192 <p>
193 If you <a href="lang_expr.html#castexpr">CAST</a> a string into a BLOB, then the entire length of the
194 string is shown. For example:
195
196 </p><div class="codeblock"><pre>SELECT a, CAST(b AS BLOB) FROM t1;
197 </pre></div>
198
199 <p>
200 Gives this result:
201
202 </p><div class="codeblock"><pre>1,X'6162630078797a'
203 </pre></div>
204
205 <p>
206 In the BLOB output, you can clearly see the NUL character as the 4th
207 character in the 7-character string.
208
209 </p><p>
210 Another, more automated, way
211 to tell if a string value X contains embedded NUL characters is to
212 use an expression like this:
213
214 </p><div class="codeblock"><pre>instr(X,char(0))
215 </pre></div>
216
217 <p>
218 If this expression returns a non-zero value N, then there exists an
219 embedded NUL at the N-th character position. Thus to count the number
220 of rows that contain embedded NUL characters:
221
222 </p><div class="codeblock"><pre>SELECT count(*) FROM t1 WHERE instr(b,char(0))>0;
223 </pre></div>
224
225 <h1 id="removing_nul_characters_from_a_text_field"><span>4. </span>Removing NUL Characters From A Text Field</h1>
226
227 <p>
228 The following example shows how to remove NUL character, and all text
229 that follows, from a column of a table. So if you have a database file
230 that contains embedded NULs and you would like to remove them, running
231 UPDATE statements similar to the following might help:
232
233 </p><div class="codeblock"><pre>UPDATE t1 SET b=substr(b,1,instr(b,char(0)))
234 WHERE instr(b,char(0));
235 </pre></div>
236 <p align="center"><small><i>This page was last updated on 2022-05-23 22:21:54Z </small></i></p>
237