It seams that binary blobs broke .mode csv export. Are there workaround? .mode insert is too verbose and it probably slower than csv import.

Side question is it possible to make .mode insert generate "insert or ignore" commands?

Define "broke". It seems perfectly fine to me ...

Sometimes quotes appear inside blobs and ruing the data integrity of csv. The imported table is broken.

Sometimes quotes appear inside blobs and ruing the data integrity of csv.

The mime type for CSV, as specified by RFC 4180¹, is "text/csv". Its section 2 clearly defines its field data as text, as copy/pasted below:

 The ABNF grammar [2] appears as follows:

   file = [header CRLF] record *(CRLF record) [CRLF]

   header = name *(COMMA name)

   record = field *(COMMA field)

   name = field

   field = (escaped / non-escaped)

   escaped = DQUOTE *(TEXTDATA / COMMA / CR / LF / 2DQUOTE) DQUOTE

   non-escaped = *TEXTDATA

   COMMA = %x2C

   CR = %x0D ;as per section 6.1 of RFC 2234 [2]

   DQUOTE =  %x22 ;as per section 6.1 of RFC 2234 [2]

   LF = %x0A ;as per section 6.1 of RFC 2234 [2]

   CRLF = CR LF ;as per section 6.1 of RFC 2234 [2]

   TEXTDATA =  %x20-21 / %x23-2B / %x2D-7E

There is no part of that which accounts for binary data. The error seems to be in the presumption that binary CSV exports are in any way valid to begin with.

1. ^{^} Which is what shell.c claims to follow, with the extension that it supports separators other than commas

I've checked it more carefully and it is zero byte broke blob export. In the csv output mode blob appear cut to the first zero byte.

This seams to be a bug in sqlite3 cli:

$ sqlite3 /tmp/temp.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> CREATE TABLE temp ( field BLOB );
sqlite> INSERT INTO temp VALUES(X'450045');
sqlite> .mode csv
sqlite> SELECT * FROM temp;
E
sqlite> .mode quote
sqlite> SELECT * FROM temp;
X'450045'

Please consider this a bug report. I do not know how to submit a bug report elsewhere.

... Please consider this a bug report.

Indeed a bug this is. The truncation of BLOB data at 0x00 byte-value in list and other .modes.

I (another anonymous) am not sure how this discussion managed to devolve from the actual issue and into the direction of "blame the user".

There is apparent inconsistency in the way the non-printable results are handled at the output.

For example:

sqlite3 --version
SQLite version 3.40.1 2022-12-28 14:03:47

sqlite> .mode
current output mode: list

sqlite> select "ABC";
ABC
sqlite> select "AB C";
AB C

sqlite> select x'414243';
ABC
sqlite> select x'41422043';
AB C
sqlite> select length(x'41422043');
4

sqlite> select x'41420143';
AB<0001>C
sqlite> select length(x'41420143');
4

As one can see, the BLOB containing 0x01 value is properly sized, and the non-printable 0x01 is output properly (per TTY capability as
"<0001>" symbol, excised here by Fossil Forum).

Now, let's try this with NUL 0x00:

sqlite> select x'41420043';
AB
sqlite> select length(x'41420043');
4

The BLOB is sized properly, yet it's truncated on the output at the 0x00 char, the 0x43='C' is missing .

In my view, the expected behavior for such case should be consistent with at least the way the NUL is handled by TTY:

$ echo -e "AB\x01C"
AB<0001>C

$ echo -e "AB\x00C"
ABC

Most importantly, silent truncation of the remaining data is not expected. The source data, be it text or bytes, should be processed as sized. In data context the NUL 0x00 character is just as ASCII as CHAR(0x01), and the length of data is sized based on the actual bytes given.

It is the treatment of NUL as C-style string terminator instead of relying on the actual size of the given data field -- however convenient this may be -- is what apparently causes this bug.

Specifically, for the default list .mode, the code that truncates the BLOB past 0x00 is the following:

src/shell.c.in:2395

utf8_printf(p->out, "%s", z); clearly stops processing the BLOB's data at 0x00 char, as it's treated here as a C-string.

Instead, the BLOB (perhaps the TEXT too) should take into account its actual length as sized. Similarly to how it's done in quote .mode, for example:

src/shell.c.in:2517

Then iterate over the defined length of the BLOB and print the bytes as characters:

for(i=0; i<nBlob; i++){ utf8_printf(p->out,"%c",zBlob[i]&0xff); }

In the same vein, to handle this in CSV .mode the output_csv() function could be extended to accept an optional len parameter, similarly to how it's done in JSON .mode.

...am not sure how this discussion managed to devolve from the actual issue and into the direction of "blame the user".

Easy - It didn't. The user was never blamed, the user was wrong and thusly educated, much like you are now wrong and about to be educated (but definitely not blamed).

In SQLite's CLI, when running a query, in order for it to be displayed on the screen or exported as text, it has to be converted to C-Strings. This is documented and it foregoes any complex/smart/special conversions and simply does the minimal needed to get it into a string. This is a huge advantage in not hiding serious data problems from the user by massaging values into more joyful screen-reading-happy or export-happy formats. It unfortunately also means that some of the caveats of using C strings will be evident, such as ending the display string when encountering the NUL character (as is well documented both in SQLite docs and every C-Strings reference out there).

If you want to prove an inconsistency, it is no use to show how character NUL is handled different from character 01 - that difference is very much a base rule of C-Strings, very well documented, very expected and very correct. You'd do better to find a case where the CLI does NOT display data as a correct C-String (with all the C-String rules applied), that would prove to be an error, an inconsistency, or indeed, a bug.

Also note that just because the C-String conversions seem to satisfy most of your other needs, or say CSV's needs, does not make it a universal proxy for satisfying those needs. i.e. if you are able to teach your parrot to bark like a dog, that doesn't mean you can now reasonably expect it to eat dogfood or lift its leg against a tree.

Finally, luckily, if you do not like this minimal C-String translation offered by the CLI when displaying/exporting your specific data, there exists a swathe of useful functions to do the special massaging you suggest would be useful to do, and even then if you still find none agreeable, you can roll your own - such is the power of SQLite.

In short then, I will rehash the old adage: It's not a bug, it's a feature.

It is a user bug.

The user is requesting that a certain course of action be done.

Exactly what is requested is being done.

What is being requested is "an error", however, the user is not prevented from being "an error", and "an error" is committed by the user, and the "erroneous" output is produced (as expected).

The user is apparently unhappy that their erroneous request had an erroneous result -- rather than make a non-erroneous request, however, the user complains that it wants some special dispensation.

If stabbing yourself in the abdomen with a sharp knife hurts and causes blood to leak all over the place (and you do not want to hurt or have blood leak all over the place), then I would suggest you stop doing stabbing yourself in the abdomen with a knife.

Perhaps the problem is an expectation problem. Somehow you seem to expect that when you stab yourself in the abdomen with a knife, something other than a stab wound that leaks blood will occur. Observation, however, indicates that your expectation was in error.

Stripping a binary blob to the first null byte is an obvious error. The sqlite3_column_bytes function is not called to query the blob length to be precise.

SQLite3 is not "stripping a binary blob to the first null byte".

This is an expectation management problem -- in particular your expectations seem to be in error.

(Edits for nit eradication.)

The CSV standard, by its terms, specifies a structured text format. This may be somewhat implicit, but that is made explicit here, where you can see that it is a form of 'MIME type "text/csv"'. It says nothing whatsoever about how to encode binary data into text to be interpreted as "CSV".

CSV is a way to structure arbitrary text fragments as a set of records, each containing a set of fields, each of which contains text or is empty.

There is no deviation from expected, documented, or sensible behavior in the CSV formatter here. As Mr. Medcalf has stated, an expectation adjustment is needed. Or, perhaps some education would help. That latter would be to work to understand that not every sequence of bytes is valid text according to common conventions for encoding characters. If you research "Unicode" and "character encoding", you can see this for yourself.

So, masking error, converting some binary blobs into csv and silently messing up some others is an expected behavior from sqlite. Ok, I have nothing to complain than. Good luck.

See https://sqlite.org/nulinstr.html for an explanation of how your expectations are incorrect.

(maybe also see post no. 24)

CSV is a way to structure arbitrary text fragments as a set of records, each containing a set of fields, each of which contains text or is empty.

Why should the command only spit CSV and not convert binary to hex or something else (at least just to prevent the whole output from breaking)?

Fixing broken CSV would not cause trouble to anyone (except to people expecting broken CSV; and always broken in an exact same way which I'm sure is not specified anywhere)

btw I am sure doing such conversion would (in the end; realistically) need some functionality to specify how the conversion is made.

This burden might be a valid reason to not do the conversion.

This person is just asking for some automatic blob escaping (which would be nice indeed, breaking the whole output just because whoops blobs were included!, seems quite sad to me)

Why be so rude? Even if automagic were not welcome, one might just say "the sqlite project does not really like automagical things and so you have to do it yoursef" (like the OpenBSD guys do sometimes) and all is well.

There is a function already in existance to do "blob escaping" -- it is called quote() -- and it provides a "text" result (as does HEX()). One could also use a function which converted the blobs to base64 text, for example, and then "convert them back" after reading them.

Or, one could use (or devise) a file format that can contain blobs (it is not CSV, which is for text only).

I think the point here is that breaking silently outputs is maybe not acceptable - not how to do escaping.

But the failure to "sanitize your inputs" is the very problem of which the OP was complaining. The OP forgot to sanitize his inputs (that is, only text data can be stored in a CSV file, so you need to convert anything that is "blob" into "text" before making the CSV), and then convert it back afterwards.

Since no one other than the person designing the "sanitizers" knows the content of the database itself such that it can devise a how to undo that which it has done, making a generic sanitization process would be designed to fail from the time of ill-conception.

A simple pointer to https://xkcd.com/327/ should have been sufficient.

A simple pointer to https://xkcd.com/327/ should have been sufficient.

This is very funny. :-)

You would be surprised how common it is the get the "LastName" from a input text box somewhere (like a web form) and then do the following:

sql = "delete from students where LastName='" + LastName + "';"
execsql(sql)

rather than

execsql("delete from students where LastName=?;", LastName)

It occurs with about the same frequency as failure to understand the difference between a "binary object" which contains text and a "binary object" which has arbitrary contents.

And thus, "sanitize your input"!

But the failure to "sanitize your inputs" is the very problem of which the OP was complaining. The OP forgot to sanitize his inputs

You don't know. Maybe yes, maybe no. Maybe he's just like me, trying to make you produce valid outputs or at least mention the preconditions in the documentation. Who knows?

making a generic sanitization process would be designed to fail from the time of ill-conception.

Producing valid output is a different thing from producing expected output. You can at least try to produce valid (non broken) output. As I said before, no one depends on broken input, so you should safely be able to make the command produce valid CSV for all inputs. OR at least specify in the (unfortunately poor) CLI docs that the command produces "successfully" broken output, and (maybe) even worse, "successful" CSV-valid but data-incorrect output (I cannot imagine a worse thing than that). At least, please produce an error.

You can at least try to produce valid (non broken) output.

Garbage in, garbage out. It helps if you don't put in garbage.

You can bloat out the SQLite code to cater for every possible eventuality, but someone somewhere will find a way of breaking it. Some just seen intentional - 'If you add 89 NULL characters then a comma, I want it to display across nine lines followed by an asterisk - can you add that too?'.

At least, please produce an error.

It did produce an error, and that error was the subject of this entire thread.

What error? Sorry if I missed it; I believe this thread is about output corruption. The error happens with what you do with that output, am I wrong?

If you want to add 74 and 22 and instead do 74 + 31 = 105 and complain that the result 105 is not the sum of 74 and 22, then an error has occurred.

The error is that the user added the wrong numbers and complained that the sum was not the same as the sum desired when adding the right numbers.

The result obtained is not in error. The question asked was an error, and the result obtained (although correct), to a lay (ignorant) observer/user, an error.

However, the correction to the error is applied with a clue-by-four to the user, not the "adding machine".

I am not talking about this error. Even if there is a user error, sqlite still pretends to produce a valid result for an unsupported output. Broken input should produce an error, at least for a CLI (high level) program. Or at least say in the docs that it's as dumb as a lawyer.

Why be so rude?

I can't speak for any of the others that were ostensibly rude in this thread (or any other), but since it's an open-ended question, I can offer some insight into when I myself employ rudeness on occasion:

When asked a perfectly legitimate question by a poster, from a position of not knowing the answer, then I am never rude, and indeed you would be hard pressed to find anyone on here that offers direct rudeness in return.

Often, though, the question is not asked sincerely nor from a position of honest lack of information, often the question is rather a statement about what the asker assumes is the real truth and wants us (the SQLIte community in this case) to understand their point, often with passive-aggressive undertones.

Such a question example is: "Why does SQLite not quote BLOBs automatically in CSV output?". The asker is not in any way interested in an answer, he/she is merely interested in impressing upon us that he/she believes that is the right thing to do and that the buggy SQLite, if it ever hopes to be a "real" player in the DB arena, better get its act together and start doing so.

The obvious answer, which has been communicated, is that CSV is not a Binary file format, and if you wish to use it, you better ensure every column is in text format. Making a binary (BLOB) column into text format is trivially easy, simply Quote() it or HEX() it, or perhaps for more pro options, roll your own or use one of the myriad of libraries available.

What you can't do, is demand that SQLite CLI's CSV mode automatically decides it for you. Let's say you wanted to really output it as Base64, would CSV mode prevent/override your decision?. What about when I wrote a custom CSV interpreter that will happily eat binary bits in the file, should SQLite now take away my right to have such output? I should be the only one to control the format, no background magic should ever happen, especially none that does NOT form part of the standard(s) governing the claimed output.

Any way, to get back to the point - sometimes the correct idea is explained, everyone is happy and moves on. Sometimes however, once one has explained the same thing in a couple different ways, and the listener is still stubbornly pursuing their notion of what should be, then it's rather hard not reiterate in a more rude tone.

Another reason, less the fault of the asker, is that new folks often forget that over the last 20 years this forum saw every argument, from umpteen different perspectives, and argued over every detail a hundred times in each direction... So sometimes you see a new someone trying to push an idea or suggest a thing that's been rehashed so many times, it's just a bit hard not to react immediately with a strongly negative tone. (Not saying this is right/good, simply that it understandably happens).

That said, this forum is typically so serene that what passes for "very rude" on this forum would be laughably mild on nearly every other forum out there. I'm quite proud of the folks here for that fact and happy to contribute in such an environment.

I'm quite proud of the folks here for that fact and happy to contribute in such an environment.

Well said, Ryan. Thanks.

The asker is not in any way interested in an answer, he/she is merely interested in impressing upon us that he/she believes that is the right thing to do

I don't think so. I believe it's the typical, "bad/simple english seems like rude english" thing.

And even if the poster were a little bit enraged (which can be understood to a certain point, because 1) broken CSV is not CSV and 2) nothing is said about exceptions breaking promises in the docs) there is nothing justifying rudeness (this is my POV)

In the end the poster just went away with a bad image of the sqlite project, you, and maybe more (the english/american speaking world? who knows?). No one is happy.

Btw this is known in some cultures/traditions as causing bad karma ;)

Broadly, I don't disagree, but the question was why rudeness happens, which is what I answered. if the question was asking if being rude is always good/just, then I would have answered no.

Also, I wasn't defending the posters or opining on the content of the OP who asked the question in this specific thread, I was merely using it as an example of the sort of question that elicits the said response. Perhaps I should have steered clear of the topic at hand when forming the examples (there's a myriad of better examples on the forum).

I don't think so. I believe it's the typical, "bad/simple english seems like rude english" thing.

I do not agree with that bit - if you are correct, why then did the conversation get quite heated? Ending with the OP leaving with the next very passive aggressive statement:

So, masking error, converting some binary blobs into csv and silently messing up some others is an expected behavior from sqlite. Ok, I have nothing to complain than. Good luck.

He wasn't misunderstood. He was belligerent and frustrated at not getting his way, with enough English language skills to get that point across.

I'm not blaming him or bemoaning his actions, or caring about it in any way. I wasn't part of the discussion even. Simply pointing out that your assessment of his intent does not fit the facts, i.e. I honestly think this was a similar case to that which I described and that any rudeness he encountered he would, at least partly, be complicit to.

I agree with you except for that bit

He wasn't misunderstood. He was belligerent and frustrated at not getting his way

That was what happened in the end; this rudeness came as a response to some other rudeness that came first.

Anyways I was implying that there's a bit too much rudeness on this forum imho (I've been following all updates since quite a while), and in the IT web in general (so I'm not targeting anyone in particular)

Sorry for the disgression.

(Quotes reordered for flow improvement.)

Why be so rude?

I disagree with the premise of this question. Looking back at all posts in this thread, excluding those apparently from the (anonymous) OP, I see no rudeness but remarkable patience and restraint in the face of dogged obtuseness.

Normally, (at least for some years now), I refrain from discussion about discussion because it is usually pointless and a distraction from useful discussion. However, I think that sometimes more atmospheric considerations need attention.

I see, among the great virtues of this forum, that regular participants are helpful technically, patient and generally kind. Or, where kindliness is out of place, there is hardly any emotionally abusive interaction. That latter is so rare here that it is clearly out of place.

I do not see assertions such as "Your expectation is wrong" or "You need to learn about something specific" as rude at all. Technical creation, programming, or using SQLite are endeavors where a pat on the head or a verbal participation trophy, given in lieu of specific advice that will help if heeded, is not really nice or likely to do any real good. When somebody's problem is that they have an incorrect or unworkable technical notion or they lack knowledge of certain facts relevant to their difficulty, it does little or no good and may be positively harmful to mask the needed correctives with "polite" softening of the bare facts.

People who go into technical fields, to be successful, must learn that they will be mistaken, misguided, or ignorant from time to time. Anybody who prefers to believe they are right over learning what is needed to deal with their technical challenges should stay out of hard technical work.

My gut reaction to the proposition that the OP has been treated rudely here is that people need to get over themselves and their feelings. It is a brutal, inescapable fact that a frequently needed fix for problems presented here is some mental adjustment(s). It is not rude to say so plainly.

This person is just asking for some automatic blob escaping ...

That's not accurate. The OP has suggested that absence of such a feature is a bug, one which may make luck useful if said "bug" is not cured.

people need to get over themselves and their feelings.

I'm nobody of importance (especially in this industry since I'm not really part of it) but I agree with you 110% and feel that this is a problem in many areas of society.

Positive reinforcement is quickly forgotten and ineffective.

Negative reinforcement is long remembered and effective.

If one's genuine intent is to learn, then one will not be overly offended by even the harshest responses if those responses provide useful information. As I'm sure many of you did also, when I was the equivalent of an apprentice, I asked as many questions as I was permitted and then shut up and listened and got working.

One would be hard pressed to find anything in the workplace as tame as this forum.

(as I started this discussion, I will take responsibility and answer properly even though I feel a bit sorry for any eventual trouble caused by the disgression)

I disagree with the premise of this question. Looking back at all posts in this thread, excluding those apparently from the (anonymous) OP, I see no rudeness but remarkable patience and restraint in the face of dogged obtuseness.

When I say "rude" I mean that, most people who answered here did not try to understand what the OP meant or felt, but went on doing lawyerish things, or answering secondary questions (omitting the primary question) as if they were primary questions (== being rude by omission).

Of course technically everyone here is right, but the point is precisely (that the OP does not understand such technicalities) OR (that the OP still feels that broken CSV is not CSV).

So going on replying without going straight to the point is like trying hard to speak english to a frenchman as if the frenchman would understand english, while knowing perfectly (but pretending to not know) that this frenchman does not understand english. (just an example so I did it with my nationality).

And even though that the question might be as simple as an RTFM problem, even for me as a picky low level programmer having been doing many things for 10+ years, this is not as simple. (btw I stand to the point that either of the following should be true 1) broken CSV is not CSV and must be fixed OR 2) invalid inputs should be specified in the docs, espacially as a single blob field can break the whole output)

dogged obtuseness

He did not get the meaning of what the replies said and/or tried to get himself understood (even though he got angry at the end); is this being obtuse? he fled right away as soon as he noticed that what he saw as a bug was not seen as one by many people here. (and maybe because he felt rudeness first..)

Or, where kindliness is out of place, there is hardly any emotionally abusive interaction. That latter is so rare here that it is clearly out of place.

My main point here is precisely that being too technical, too lawyerish, and answering indirectly (cleverly) is as bad as being emotional. Or even worse, because when you build walls (while pretending the walls do not exist), the real conversation does not even take place (while the bad effects all remain).

People who go into technical fields, to be successful, must learn that they will be mistaken, misguided, or ignorant from time to time. Anybody who prefers to believe they are right over learning what is needed to deal with their technical challenges should stay out of hard technical work.

I agree fully. But this is true only when real conversations take place.

In this thread the OP is stubborn about his problem "not being recognized as a bug obviously and immediately" while repliers are stubborrn about "not recognizing that, or not explaining why not, converting binary to text before spitting CSV does nothing bad to anyone and doesn't break any promises".

Just for the sake of it, I will try answering instead of the OP (where the conversation broke at no. 13), so (only) those who wish, bear with me :)

people need to get over themselves and their feelings

I agree fully again. but it is not an excuse to be rude to simple minded people. (just like spitting out logic and complicated lectures to small children who do not have yet the brain cells (of the 3rd brain) to understand them only damages personnality and other deeps things - the point here is not what the Other should be but how You, knowing more, should be. And this is not religion even if it looks like it)

That's not accurate. The OP has suggested that absence of such a feature is a bug, one which may make luck useful if said "bug" is not cured.

Well yes, the OP did not ask for automatic escaping directly, but in the end this is what the OP wants (and you know it).

Not especially towards you only, but if you wish to be a lawyer when writing, then be also a lawyer when listening ;)