[sword-devel] Per Project news: - was Re: CrossWire news

[DM Smith]

On 02/14/2011 09:04 AM, Greg Hellings wrote:
>
> Sounds like you are manually constructing your SQL instead of using a 
> prepared statement with bound variables? That should take care of the 
> problem.
>

If Greg is right, bound variables will help avoid SQL injection.

For those not familiar with SQL injection, the concatenation of parts 
into a query can lead to storage of bad stuff in the database.
E.g.
Take
'SELECT t.data FROM my_table t WHERE t.id = "' + theId + '"';
where theId is input from the outside, say a webpage.

A clever user could supply the following for theId:
1" OR 1=1; DELETE FROM my_table WHERE "0" = "0

The resultant query would be:
SELECT t.data FROM my_table t WHERE t.id = "1" OR 1=1; DELETE FROM 
my_table WHERE "0" = "0"

If executed, the table would have all it's contents wacked.

It is also possible to replace the DELETE with an INSERT INTO, CREATE 
TABLE, ..... OR anything else that the caller has privileges for.

A typical way to protect from this is to use bind variables and also to 
have a separate user account with read-only privs. Another good practice 
is to use JDBC to execute single statements rather than a batch.

The statement above w/ bound variables would look something like (syntax 
varies between databases and jdbc):
'SELECT t.data FROM my_table t WHERE t.id = :id';
or maybe
'SELECT t.data FROM my_table t WHERE t.id = ?';

Also, the error message represents information bleeding. It tells the 
user that Firebird is the database. A hacker could then lookup exploits 
for that database. It is typically a best practice to have exception 
blocks that upon error give nothing informative to the user, except that 
the request could not be handled at the moment.

Hopefully, this is constructive.

In Him,
     DM

> --Greg
>
> On Feb 14, 2011 7:32 AM, "Ben Morgan" <benpmorgan at gmail.com 
> <mailto:benpmorgan at gmail.com>> wrote:
> > Vary the following:
> > 
> http://www.crosswire.org/pages/crossnewstest.jsp?project='&class=2&percat=3&maxRes=6 
> <http://www.crosswire.org/pages/crossnewstest.jsp?project=%27&class=2&percat=3&maxRes=6>
> >
> > org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. 
> Dynamic SQL
> > Error SQL error code = -104 Unexpected end of command - line 1, 
> column 379
> >
> > Other than that (and a number of variations on it), it seems to work 
> (i.e.
> > for validly constructed URLs it works).
> >
> > God Bless,
> > Ben
> > 
> -------------------------------------------------------------------------------------------
> > Multitudes, multitudes,
> > in the valley of decision!
> > For the day of the LORD is near
> > in the valley of decision.
> >
> > Giôên 3:14 (ESV)
> >
> >
> >
> > On Tue, Feb 15, 2011 at 12:23 AM, Peter von Kaehne <refdoc at gmx.net 
> <mailto:refdoc at gmx.net>> wrote:
> >
> >> I have now in a test file added the code to have a project parametre.
> >>
> >> You can see it here in action:
> >>
> >>
> >> 
> http://www.crosswire.org/pages/crossnewstest.jsp?project=8&class=2&percat=3&maxRes=6 
> <http://www.crosswire.org/pages/crossnewstest.jsp?project=8&class=2&percat=3&maxRes=6>
> >>
> >> Please test it and if you manage to crash it, tell me what i did 
> wrong. The
> >> underlying sql statement is pasted to the bottom of the page 
> underneath.
> >>
> >> The idea is that you can combine news from your particular project 
> and news
> >> from e.g the "module factory", which I guess is nice to advertise your
> >> particular frontend and the modules you can display (which 
> obviously won't
> >> work if your frontend has display or logic deficiencies)
> >>
> >> There are a few half or totally abandoned projects strewn across 
> the site
> >> and i will add it there to show how it would look for them. Others 
> can then
> >> see if they want to make use of the facility.
> >>
> >> I will also in the next few days try and make the production of 
> tailored
> >> rss feeds similarly straight forward and then throw a bunch of such 
> feeds in
> >> a well accessible place - particularly also for externally hosted 
> projects
> >> this might be of use.
> >>
> >> Peter

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.crosswire.org/pipermail/sword-devel/attachments/20110214/2806dba4/attachment.html>