StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You are victim of SQLite <a href="http://www.sqlite.org/datatype3.html" rel="nofollow">dynamic typing</a>.</p> <p>Even though SQLite defines system of <a href="http://www.sqlite.org/datatype3.html#affinity" rel="nofollow">type affinity</a>, which sets some rules on how input strings or numbers will be converted to actual internal values, but it does NOT prevent software that is using prepared statements to explicitly set any type (and data value) for the column (and this can be different per row!).</p> <p>This can be shown by this simple example:</p> <pre><code>CREATE TABLE ledger (acctno INTEGER, name VARCHAR(16)); INSERT INTO ledger VALUES(1, 'John'); -- INTEGER '1' INSERT INTO ledger VALUES(2 || X'00', 'Zack'); -- BLOB '2\0' </code></pre> <p>I have inserted second row not as INTEGER, but as binary string containing <em>embedded zero byte</em>. This reproduces your issue exactly, see this <a href="http://www.sqlfiddle.com/#!5/10fc5/3" rel="nofollow">SQLFiddle</a>, step by step. You can also execute these commands in <code>sqlite3</code>, you will get the same result.</p> <h1>Below is Perl script that also reproduces this issue</h1> <p>This script creates just 2 rows with <code>acctno</code> having values of integer <code>1</code> for first, and <code>"2\0"</code> for second row. <code>"2\0"</code> means string consisting of 2 bytes: first is digit <code>2</code>, and second is <code>0</code> (zero) byte.</p> <p>Of course, it is very difficult to visually tell <code>"2\0"</code> from just <code>"2"</code>, but this is what script below demonstrates:</p> <pre><code>#!/usr/bin/perl -w use strict; use warnings; use DBI qw(:sql_types); my $dbh = DBI->connect("dbi:SQLite:test.db") or die DBI::errstr(); $dbh->do("DROP TABLE IF EXISTS ledger"); $dbh->do("CREATE TABLE ledger (acctno INTEGER, name VARCHAR(16))"); my $sth = $dbh->prepare( "INSERT INTO ledger (acctno, name) VALUES (?, ?)"); $sth->bind_param(1, "1", SQL_INTEGER); $sth->bind_param(2, "John"); $sth->execute(); $sth->bind_param(1, "2\0", SQL_BLOB); $sth->bind_param(2, "Zack"); $sth->execute(); $sth = $dbh->prepare( "SELECT count(*) FROM ledger WHERE acctno = ?"); $sth->bind_param(1, "1"); $sth->execute(); my ($num1) = $sth->fetchrow_array(); print "Number of rows matching id '1' is $num1\n"; $sth->bind_param(1, "2"); $sth->execute(); my ($num2) = $sth->fetchrow_array(); print "Number of rows matching id '2' is $num2\n"; $sth->bind_param(1, "2\0", SQL_BLOB); $sth->execute(); my ($num3) = $sth->fetchrow_array(); print "Number of rows matching id '2<0>' is $num3\n"; </code></pre> <p>Output of this script is:</p> <pre><code>Number of rows matching id '1' is 1 Number of rows matching id '2' is 0 Number of rows matching id '2<0>' is 1 </code></pre> <p>If you were to look at resultant table using any SQLite tool (including <code>sqlite3</code>), it will print <code>2</code> for second row - they all get confused by trailing 0 inside a BLOB when it gets coerced to string or number.</p> <p>Note that I had to use custom param binding to coerce type to BLOB and permit null bytes stored: </p> <pre><code> $sth->bind_param(1, "2\0", SQL_BLOB); </code></pre> <p>Long story short, it is either some of your client programs, or some of client tools like Navicat which screwed it up.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload