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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3
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".

<https://www.helpnetsecurity.com/2017/04/21/programming-tutorials-vulnerabilities/>

“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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

J. King-3
On April 23, 2017 4:31:42 PM EDT, Simon Slavin <[hidden email]> 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".
>
><https://www.helpnetsecurity.com/2017/04/21/programming-tutorials-vulnerabilities/>
>
>“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
>[hidden email]
>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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Richard Hipp-3
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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Richard Hipp-3
On 4/23/17, Simon Slavin <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Keith Medcalf
In reply to this post by Simon Slavin-3

> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3

On 24 Apr 2017, at 2:33am, Keith Medcalf <[hidden email]> 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 = '999999.99"
—> UPDATE accounts SET phone = '5551234', balance = '999999.99'

didn’t trouble me until SQL appeared.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Dominique Devienne
In reply to this post by Keith Medcalf
On Mon, Apr 24, 2017 at 3:33 AM, Keith Medcalf <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

James K. Lowden
In reply to this post by Simon Slavin-3
On Sun, 23 Apr 2017 21:31:42 +0100
Simon Slavin <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users