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

[James K. Lowden]

On Sun, 23 Apr 2017 21:31:42 +0100
Simon Slavin  wrote:

> If you don?t understand what you?re doing, hire an experienced
> programmer.  

Ah, but you don't know what you don't know.  After all, 90% of
programmers rate themselves "above average".  

When I first heard of "SQL injection" years ago, I started looking into
it, of course.  Every single one I read about could have been prevented
by following two simple, well known rules:

1.  Every database access must be through stored procedures. 

2.  The process accessing the database must have no rights to
the database except through stored procedures.  

(SQLite can't provide the same degree of protection because it doesn't
offer process separation.  That makes it inappropriate for some
applications.  OK.)

For SQL injection to be a problem requires the whole technical
organization to neglect to protect the data.  That criminals try to
steal data is no surprise.  That so-called professionals abet them
through neglect borders on malfeasance.  

--jkl
___
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..

[Dominique Devienne]

On Mon, Apr 24, 2017 at 3:33 AM, Keith Medcalf  wrote:

> > I’m curious about binding as an idea. [...]
> [...] The EXEC SQL interface has all but disappeared in most languages
> [...]


Oracle still supports https://en.wikipedia.org/wiki/Pro*C
but that's pure client-side, while it seems your early EXEC SQL
"precompilers"
were both client-side *and* server-side.

An advantage of Pro*C is that it checks at pre-compilation-time the SQL text
against the schema of the database. But that's also it's downfall, since no
one
wants to ties its CI and builds in general to contacting an Oracle
database, nor
is it necessarily practical to have the schema your targeting instantiated
server-side.

A big inconvenience is that other software tools (like IDEs, linters,
etc...) don't like
"embedded DSLs" in a "foreign language" compared to the "host" language.
--DD

PS: I write the above, but then my CI runs unit tests against Oracle, so
that's not that different :)
___
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]

On 24 Apr 2017, at 2:33am, Keith Medcalf  wrote:

> [history]

That’s very interesting.  I’m remembering the first DBMS language I used on 
desktop computers rather than mainframes or minis.  It was something called 
"Q-Pro 4" and included both database commands and user interface commands.  
Just as you describe Sybase, there was no opportunity for injection because 
there was no way to construct a database command from a string.  Variables 
could lead only to values for storage and searching, not to entire commands.

The company used Q-Pro 4 for financial programs for years without problems.  We 
had only one hacking attempt by an employee of one of our customers.  It was 
initially successful because the database files stored data in a very simple 
all-text structure.  It was found out because the hacker didn’t update the 
accompanying index files and a few days after the hack the program complained 
about a corrupt index.  Had the hacker known, he could have had the software 
update each index file with one simple command for each one, but he didn’t have 
the language documentation or take the time to explore how it worked.  
Alternatively he could have written a little Q-Pro program to do the updating 
instead of (we guessed) using a text editor.

And that, folks, was the beginning of my interest in computer security.

Injection vulnerabilities had to wait for free form English-like database 
commands.  Not certain when that was.  I’m betting that SQL wasn’t the first 
language described that way, but I don’t remember what was.  Certainly, 
problems like

execute ("UPDATE accounts SET phone = '$p'")

$p <-- "5551234"
—> UPDATE accounts SET phone = '5551234'

$p <-- "5551234', balance = '99.99"
—> UPDATE accounts SET phone = '5551234', balance = '99.99'

didn’t trouble me until SQL appeared.

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..

[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