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
