A quick test on QGis showed that their query filter interface works the
same as ours:
the formula editor generates a query based on user input, then wraps it
into a LIMIT 0 clause, without performing any other test (";" in the query
for instance).This filter, for instance, is executed and drops the table: "code_dept" like '3%'; drop table departement_3950; select 1 In SpatialDatabases code, we also wraps the query into a LIMIT 0 clause when testing for the WHERE clause. We could try to detect ";" in queries, as it is the default query separator (but not the only one) in most databases, and is often used in SQL injection code. But i'm quite sure it won't protect OJ for any kind of injection. Nicolas On 5 January 2016 at 12:45, Nicolas Ribot <nicolas.ri...@gmail.com> wrote: > Hi all, > > Concerning SQL, I can't see how we can protect from malicious SQL code > considering: > > OJ is not just an interface to SQL databases, but a complete SQL client > allowing to perform ANY kind of queries (as we open a connection to a > database then execute the statement in DB Query plugin, for instance). > Adhoc queries works the same: free SQL query to execute. > > To use PreparedStatements, we would have to parse a free query, which is > really complicated (see the PG SQL parser for instance) and extract column > names/positions and values to bind parameters. > > I see OJ a bit like PgAdmin for Spatial Types when working with PostGIS: a > SQL client allowing DB admin to perform any task, even dangerous one. > For instance, when I use DB Query, I sometimes execute function definition > SQL as part of my SQL scripts. These functions can contain strings that > look like malicious code (when generating batch series of > delete/update/insert queries based on Catalog metadata). In this case, it > would be very hard to choose between legitimate and malicious code. > > QGis, through DB Manager, works the same: the user query is sent as-is, > and its the DB admins task to allow or not a safe connection to a database. > > Nicolas > > > On 3 January 2016 at 16:22, <edgar.sol...@web.de> wrote: > >> On 01.01.2016 19:18, Rahkonen Jukka (MML) wrote: >> > Hi, >> > >> > As far as I can imagine the security risk can only become actual if >> OpenJUMP is used in a multiuser environment where some project >> administrator is creating JUMP project files and/or workbench-state.xml >> file and deliver them for the operators. In that case users do not >> necessarily know the password that OpenJUMP is using for accessing the >> database because it is not saved in plain text into those files. >> > >> > <mapping> >> > <key class="java.lang.String">Password</key> >> > <value >> class="java.lang.String">$.........</value> >> > </mapping> >> >> nothing against your imagination here ;), but there are more ways.. etc. >> let's focus on the attack surfaces and not the routes a villain use. >> >> what you describe above is true, but essentially for all data a user has >> in a "protected" file system. hence when somebody else gains access to that >> data, all is lost. >> >> you deduce correctly that simply not saving the passwords would be the >> way to go here, we should probably add an option to db datastores. using >> pseudo encryption is of no use here, as the code to decrypt would reside in >> OJ. >> >> but all that has nothing to do with my original point - securing sql >> statements. >> >> > >> > If users do know the password they can do anything that is possible for >> that user account directly with PSQL and patching OpenJUMP would not make >> the whole system much more safe. >> >> right, and users who are not supposed to write to the db should have r/o >> access in the first place :) >> >> but leaving all that aside. i simply see the need for prepared statements >> or at least consequent sql-escaping *all* parameters, generated or loaded. >> >> free text querying is of course in the hand of the user and we can do >> nothing about it. >> >> > When it comes to WFS, it does open SELECT access and if service >> provider allow WFS-T, also UPDATE and DELETE access to the feature types in >> the service. It is relatively easy to build heavy WFS filters which could >> be used for Denial of service attacks but if belongs to WFS 1.0 and 1.1.0 >> versions and it is hard to handle the risk on the client side. In WFS 2.0 >> it is possible to configure the service so that free ad hoc queries are not >> allowed and offer only Stored Queries for the users. >> > >> > I do not believe that anybody is running WFS-T in production systems >> without some authentication and authorization. WFS standard does not tell >> how to do that so everybody is using more or less tailor made systems for >> that. >> > >> > With WMS I suppose that nasty users could really do DoS attacks with >> suitably formatted SLD files. OpenJUMP does not have any easy way for >> attaching SLD files into WMS requests and attackers would most probably use >> some other tool instead. >> > >> > I think there is no need to try to make WMS of WFS safer. With >> databases it might be useful to validate the SQL requests somehow but >> somehow I feel that most OpenJUMP users who use databases also know >> usernames and passwords. Easiest way for improving security could actually >> be to make it possible to save the database connections into project files >> and workbench-status.xml without passwords and let users optionally feed >> password manually when they reconnect. >> >> i agree that WMS/WFS write access is more unlikely to be misused, because >> of the difficult protocols. but whatever the backend, the issues are >> generically the same. let me paint a picture. >> >> - OJ loads a dataset retrieved from an untrusted 3rd party >> - OJ-User writes that dataset to the backend >> - the dataset contains malicious data values that take advantage of >> missing escaping in OJ >> eg. to write/modify data in the backend with the users permissions >> or >> crash the server software (maybe running their own code via stack >> overflow) >> >> ..ede >> >> >> > -Jukka Rahkonen- >> > >> > Michaël Michaud wrote: >> > >> > Hi Ede, >> > >> > You're right, our database code is probably not safe. >> > It is quite easy to inject DDL instructions like create, drop or >> truncate both with FilterQuery and with AdhocQuery (it throws an error, but >> execute the DDL first ;-(). >> > >> > It is probably not an easy task to make it more safe and to keep the >> current flexibility. For me, flexibility is more important than security, >> but of course, it depends on the usage. >> > >> > Any idea how to make the code more robust without sacrifying >> flexibility ? >> > Seems to me that using prepared statements and escaping would not apply >> well to AdhocQuery. >> > Maybe another solution would be to parse the query with a sqlparser >> first and remove anything containing something else than a select. >> > >> > Michaël >> > >> > Le 31/12/2015 17:06, edgar.sol...@web.de a écrit : >> >> to all concerned especially Nico, Mike, Jukka... >> >> >> >> when working on the db datastores i became aware that there are >> >> - no prepared statements >> >> - no sql escaping of parameters at all . >> >> >> >> coming from a background of web development and being generally >> security aware that troubles me somewhat. give the possibility that users >> might open foreign project/data files, containing arbitrary strings (eg. >> col names) this looks like it could be misused. >> >> afaics, just because we only read exception being postgres, does not >> mean that the connection wouldn't happily write something when the query >> string suggests so. >> >> >> >> what is your standpoint on that? >> >> >> >> similar issues are probably to be found in WMS/WFS, where i also >> didn't see much escaping when building queries. >> >> >> >> ..ede >> >> >> >> ---------------------------------------------------------------------- >> >> -------- _______________________________________________ >> >> Jump-pilot-devel mailing list >> >> Jump-pilot-devel@lists.sourceforge.net >> >> https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel >> >> >> > >> > >> > >> ------------------------------------------------------------------------------ >> > _______________________________________________ >> > Jump-pilot-devel mailing list >> > Jump-pilot-devel@lists.sourceforge.net >> > https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel >> > >> > >> ------------------------------------------------------------------------------ >> > _______________________________________________ >> > Jump-pilot-devel mailing list >> > Jump-pilot-devel@lists.sourceforge.net >> > https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel >> > >> >> >> ------------------------------------------------------------------------------ >> _______________________________________________ >> Jump-pilot-devel mailing list >> Jump-pilot-devel@lists.sourceforge.net >> https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel >> > >
------------------------------------------------------------------------------
_______________________________________________ Jump-pilot-devel mailing list Jump-pilot-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
