yes, 1 and the string '1' are not the same. previously we use oracle,and now we use sqlite for better perfermance。 in same sql,we usually like this select * from test where id = 1 and '%s'=1 it works in oracle,but when switch some business to sqlite ,problem comes。

actually，code like '%s'=1 is bad,it just works in mysql or oracle.

is there any suggestion? or the only thing we can do is change the code '%s'=1 to '%s'='1'

we use '%s' = 1 or '%s' = other num.the num is usually a dictionary. when the user click button for example ,the %s will be replace to real data。

'%s'=1 is not used to keep a always true condition。our sql is dynamic。

I see this a lot in queries generated from Oracle

This is just terribly bad lazy programming. Rather than building a valid SQL string, it's 'quicker' to just append OR or AND statements to an existing SQL string. Urgh.

This is just terribly bad lazy programming.

Well, I don't agree. Imagine you have a search form with a bunch of textboxes, and you allow users to enter in these textboxes strings to be searched. And the more strings they enter, the more restrictive the query (data-wise would be). I.e. In the last name textbox the user can type 'cabrera' and in the state textbox the user can type 'ny', but the rest of the textboxes are empty, when you write the select your select would start,

SELECT a.* FROM Project_List AS a
WHERE 1 = 1

and then a series of data from textboxes can be added to the select queries by doing something like this,

        if (txtPMLastName.text != null)
        {
          q ~= " AND lower(pmLName) = '" ~ std.string.tolower(txtPMLastName.text) ~ "' ";
        }
        if (txtClarityPM.text != null)
        {
          q ~= " AND lower(techPM) LIKE '%" ~ std.string.tolower(txtClarityPM.text) ~ "%' ";
        }
        if (txtState.text != null)
        {
          q ~= " AND lower(pmState) LIKE '%" ~ std.string.tolower(txtState.text) ~ "%' ";
        }

so the final query would have all of the data where the user entered text as part of the query. However, I do agree that 1 = '1' is not how I would do it.

Well, I don't agree.

I refer you to my main point. It's lazy.

You can write it perfectly valid without the '1=1' part. Starting with '1=1' is superfluous. It doesn't add value to the query. It works without it. Its unnecessary.

Please show me how you would create the WHERE part of the SELECT search without any extra programming code.

SELECT * FROM t WHERE
AND a = 'a'
AND b = 'b'
;
sqlite> select * from t WHERE
   ...> and a = 'a'
   ...> and b = 'b'
   ...> ;
Run Time: real 0.000 user 0.000000 sys 0.000000
Parse error: near "and": syntax error
  select * from t WHERE and a = 'a' and b = 'b' ;
                        ^--- error here

This is would error out. How you fill the query programmatically? I would appreciate it. Thanks.

While "good" programming is highly contested and often personal, the example is precisely an example of bad/lazy programming, more resembling a copy-pasted set of commands.

I would go so far as to say it isn't programming, it's a written-out list of commands, more suited to a typewriter than a computer.

Somewhat more sane (though not perfect) would be something that removes the repetitive nature of it and the unneeded string segments and useless SQL filter checks, perhaps something pseudo-coded like:

var string query = 'SELECT * FROM table';
var string whereClause = '';


...

function void addFilter(string lhs, string rhs, bool anyStr) {
    if (lhs == null || rhs == null || rhs == '') return;
    if (whereClause != '') { whereClause += ' AND ' }
    if (anyStr)  { rhs = '%' + rhs + '%' }
    whereClause += ' lower(' + lhs + ') = ' + std.string.tolower(rhs);
    return;
}

addFilter('pmLName', txtPMLastName.text, False);
addFilter('techPM',  txtClarityPM.text,  True );
addFilter('pmState', txtState.text,      True );

if (whereClause != '') { query += ' WHERE ' + whereClause; }

...

Sure, more effort to make, but now the operational differences between the filtered items are much clearer to you and future viewers of your code, and it produces a query filter without several meaningless ghost items, if any. Adding more filtered items becomes significantly simpler and also, want to change how the where-clause is built? Now you have 1 single place to fix, not 3 places.

"Programming" is how we get a computer to do repetitive work for us. It's not much good for anything else.

That is the problem. I don't know if last name textbox is filled. It may just the the state field filled, or the project number, or... I understand what you are saying, but, thanks for the explanation.

Well yes, but I believe the proposed code handles that case very well. If any of the boxes are unfilled (making "rhs" either null or empty string), then that filter will not be added, and if in the end there are no filters added at all, the where clause is simply omitted. That is how I believe it should work.

Thanks. I will give it a try. Appreciate the help. I have some down time this week.

Oh ok, since you will actually use it, I decided to improve it a bit (edited the original) from my quick-fire initial reply. It's not tested, so prepare for something to glitch, but you should be able to easily correct any such mistake.

That is pretty lazy (and bad) programming.

One would create a list of conditions for the user variables. These conditions would be joined by ' AND ' and prefixed with ' WHERE '. This means that if there were no conditions, there would be no where clause.

I do and did that all the time. Furthermore, the condition would be var == ? and the actual data would be bound (sanitized to be data).

Anyone who builds a command tail as you described (and it sounds like that is how Oracle is doing it) should be keelhauled and we will all stop for a toddy while the perp is halfway along the keel!

... WHERE id = 1 AND CAST('%s' AS INT) = 1

I like cast(), especially where clarity may be an issue, or to draw attention to the conversion. Yet there are times when a query could become cumbersome with many casts. In those cases, another option is....

WHERE id = 1 AND '%s'+0; -- make text numeric so it can compare to 1/0

Related thread

I'm sorry this isn't working out for you.

I turns out that most SQL database engines think that 1='1' is true. I don't know why. SQLite is different. An integer is not a string, to SQLite's mind, and so 1='1' is false.

SQLite has been this way for 20 years. I don't think it is reasonable to change it at this point - too much legacy code might break.

The anomalous behavior has now been documented on the Quirks Page.

On the quirks page, within that new entry, "Destinguishes" could be spelt more correctly.

may be oracle pamper programers, and other sql database follow。

now that sqlite engines think that 1='1' is false ,and sqlite results any integer less than any text. can sqlite returns some callback, for example to tell programers interger compare text is meaningless。 in sqlite SQL compare integer with text is wrong ,although sqlite returns results ,it's unpredictable for any business code.

sqlite just give a friendly tip to programers.

See https://sqlite.org/datatype3.html for the friendly tips.