Re: [sqlite] Security vulnerabilities prevalent in web tutorials for PHP, javascript, etc..

[Keith Medcalf]

> I’m curious about binding as an idea.  I never used SQLite 1 or 2.  Was
> binding originally done to avoid security vulnerability or was that just
> the result of implementing it for some other reason ?

If you are talking about things other than SQLite (which is very new to the 
scene as computerized DBMS goes), binding was done as a consequence of the EXEC 
SQL interface of most programming languages and how SQL was compiled into 
programming source code.  

Before the early versions of Sybase (1990's era) there was no such thing as 
"Dynamic SQL" where an SQL text statement was passed to the DBMS (as a string) 
to be prepared "dynamically".  SQL statements were embedded directly in the 
source programming language using an EXEC SQL (or other pre-compiler 
directive).  A pre-processor "extracted" the SQL statements, compiled 
(prepared) them, and stored them as parameterized packages in the database 
itself, and generated "pre-processed" source code which called (interfaced 
with) those packages.  

The compilation unit of the EXEC SQL statements and the source program were a 
single package and you had to recompile the whole kit-and-kaboodle to 
re-optimize the SQL Query itself (some DBMS would allow you to "rebind" an 
existing package without recompiling from source -- though that was a "feature" 
much later added, probably in the mid to late 80's).  

Twenty years on Sybase came along in the 1990's and supported dynamic statement 
preparation -- effectively using a dynamic "prepare" passed a string value to 
dynamically create the package to be executed and requiring the programmer to 
"manually create" the package binding code in the source.  This is the 
interface that is everywhere now.  The EXEC SQL interface has all but 
disappeared in most languages (though there was an interim period in the 90's 
where the EXEC SQL preprocessor would generate code which created a dynamic 
interface to the DBMS).  

Then along came the standardized CLI interfaces which used the 
prepare/bind/step/fetch model in use today.  Some called it the CLI 
(IBM/Oracle/Sybase) and others called it things like ODBC.  But they are all 
the same with very few differences.

When the EXEC SQL preprocessors went "out of favour", as it were, because the 
(a) the interface code was easy enough to generate by hand; and, (b) DBMS 
vendors were too "cheap" to write pre-processors for every language, is when 
laziness induced bad programming habits introduced the "SQL Infection" 
vulnerability which prior to the 1990's was impossible to create, even if one 
wanted to, since the text of the SQL was embedded in the source code and 
compiled by a pre-processor.  There was no way to execute "user provided" SQL 
statements even if one wanted to.





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Security vulnerabilities prevalent in web tutorials for PHP, javascript, etc..

[Richard Hipp]

On 4/23/17, Simon Slavin  wrote:
> I’m curious about binding as an idea.  I never used SQLite 1 or 2.  Was
> binding originally done to avoid security vulnerability or was that just the
> result of implementing it for some other reason ?

Binding was added to make the TCL interface work better.  Remember:
SQLite is a TCL extension that escaped into the wild.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Security vulnerabilities prevalent in web tutorials for PHP, javascript, etc..

[Simon Slavin]

I’m curious about binding as an idea.  I never used SQLite 1 or 2.  Was binding 
originally done to avoid security vulnerability or was that just the result of 
implementing it for some other reason ?

Looking at the work I did on other languages which use English-like commands I 
never used binding before SQLite.  It was always just a case of text 
concatenation.  But the apps I wrote were never exposed outside the company I 
wrote them for.  Now everything’s on the web I have to worry about J Random 
Hacker getting at my API.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Security vulnerabilities prevalent in web tutorials for PHP, javascript, etc..

[Richard Hipp]

Recall that SQLite was original created as a Tcl (https://www.tcl.tk/)
extension.

Using TCL, the first example reported in the article would be coded like this:

  set result [db eval {SELECT count(*) FROM users WHERE userid=$_POST(newid)}]

With the TCL interface to SQLite, the code above is *not* an SQL
injection.  Because the SQL statement is enclosed in {...} the
$_POST(newid) is expanded but is passed to the SQLite parser as a
parameter.  Then before the SQL statement is run, the value in the
$_POST(newid) TCL variable is bound to the parameter with the same
name.

SQLite understands TCL-style variable names as parameters in SQL
statements, for exactly this reason.

It is still possible to get an SQL injection using the TCL interface
(for example, by enclosing the SQL statement in "..." instead of
{...}) but you almost have to try to make the error with TCL.  It are
less likely to make an SQL injection error by mistake.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Security vulnerabilities prevalent in web tutorials for PHP, javascript, etc..

[J. King]

On April 23, 2017 4:31:42 PM EDT, Simon Slavin  wrote:
>There’s been almost no traffic on this list this weekend so I don’t
>feel too bad posting something that’s not specifically about SQLite. 
>But a lot of us use SQLite as a back end for web-facing databases,
>called from PHP, and this is about PHP tutorials found on the web.
>
>ObAcronym: "SQLi" is short for "SQL injection".
>
>
>
>“Thanks to our framework, we have uncovered over 100 vulnerabilities in
>web application code that bear a strong resemblance to vulnerable code
>patterns found in popular tutorials. More alarmingly, we have confirmed
>that 8 instances of a SQLi vulnerability present in different web
>applications are an outcome of code copied from a single vulnerable
>tutorial,” they noted. “Our results indicate that there is a
>substantial, if not causal, link between insecure tutorials and web
>application vulnerabilities.”
>
>Moral: Web tutorials are for teaching you how a computer language
>works.  Don’t copy-and-paste them into production code without thinking
>through the consequences.  If you don’t understand what you’re doing,
>hire an experienced programmer.  That’s what they’re for.
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

How depressing, that people still fail to learn the lessons of SQL injection 
because others fail to teach them. 

I'm currently writing a PHP application which uses SQLite, and I am of course 
paranoid about using prepared statements; I'd love it if we could all get the 
basics right so that articles warning about less straightforward problems would 
stand out more. 

Hopefully that article will reach some of those who need it. Thanks, Simon. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Security vulnerabilities prevalent in web tutorials for PHP, javascript, etc..

[Simon Slavin]

There’s been almost no traffic on this list this weekend so I don’t feel too 
bad posting something that’s not specifically about SQLite.  But a lot of us 
use SQLite as a back end for web-facing databases, called from PHP, and this is 
about PHP tutorials found on the web.

ObAcronym: "SQLi" is short for "SQL injection".



“Thanks to our framework, we have uncovered over 100 vulnerabilities in web 
application code that bear a strong resemblance to vulnerable code patterns 
found in popular tutorials. More alarmingly, we have confirmed that 8 instances 
of a SQLi vulnerability present in different web applications are an outcome of 
code copied from a single vulnerable tutorial,” they noted. “Our results 
indicate that there is a substantial, if not causal, link between insecure 
tutorials and web application vulnerabilities.”

Moral: Web tutorials are for teaching you how a computer language works.  Don’t 
copy-and-paste them into production code without thinking through the 
consequences.  If you don’t understand what you’re doing, hire an experienced 
programmer.  That’s what they’re for.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Difference between localtime and utc is 8 hours, but should be 4

[Chris Waters]

As Stephen observed when replying to your query, time math is fraught with 
problems.

So I think it will be nothing to do with SQLite per se. I'm guessing it will be 
in the time offset specification you have entered somewhere, for your 
environment. You are in what is termed time zone utc  -4.
Time zones are expressed as what you have to do to UTC to get your local time. 
So for you, -4. 
A common error is to express it as what you have to do to your local time to 
get UTC. That expression would be +4 for you! Wrong.

I suspect this is where your 8 hour difference is coming from. 

In a layered environment where you have O/S, plus "international" application 
layers such as mail environments and ERPs you have multiple competing 
transforms, which in a round trip (my location back to my location) will appear 
as everything is set correctly, but when you move out of your zone can appear 
bizarre.

Hope this is useful
Chris
> 
> I'm in the Eastern US time zone, in daylight savings time.  I am four hours 
> earlier than UTC time.  I have a column that stores UTC times as Julian times 
> (floating-point numbers).  The latest data point in the table was stored at 
> about 8:41 this morning (4/21).  
> 
> I am getting strange results from this query:
> select max(value_timestamp), 
> datetime(max(julianday(value_timestamp)), 'localtime'),
> datetime(max(julianday(value_timestamp)), 'utc') from trend_data
> 
> The results are: 
> 2457864.86179398
> 2017-04-21 04:40:59
> 2017-04-21 12:40:59
> 
> How is it that switching from local time to UTC gives an eight-hour 
> difference?
> 
> Thank you very much.
> 
> RobR
> 
> --
> 
> 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_OMIT_* vs amalgamation

[Kim Gräsman]

On Wed, Apr 19, 2017 at 1:18 PM, Richard Hipp  wrote:
> On 4/19/17, Kim Gräsman  wrote:
>> I'm building for both Windows and Linux and it's a reproducible build,
>> so I'd rather not depend on anything extra.
>
> The only thing extra you need is tclsh installed as it is used during
> the build process.  Besides TCL, just a C compiler and the ordinary
> tools.  On unix, just run "./configure; make".  On Windows with MSVC
> the command is "nmake /f makefile.msc"

OK, thanks! I'll see what I can do.

- Kim
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users