modifying data for a new row

aziegler3

I work with spatial data. I have an oracle database that has an underlying set of libraries with the name of SDE.
My table has a spatial component and a whole lot of non spatial data that is treated as any normal database data.

This query works:

$sql = "insert into table_name "
    ."(OBJECTID, GLOBALID, shape, site_name,site_location,"
    . etc, etc
    ." degree_isolation, site_id_of_pair_rc, countyfip, countyname) "
    ."VALUES ("
    ."sde.gdb_util.next_rowid('gis', 'table_name '),"
    ."sde.gdb_util.next_globalid(),"
    . etc, etc.

I used it many times.
sde.gdb_util, etc etc live under the database in the database machine.

My intention is to catch the initSubmit event, add then add the (literally this text): sde.gdb_util.next_rowid('gis', 'table_name') as data and then submit.

Do you see anything in the inner works of Editor that might not like this approach? Common sense makes me think that it should work.

aziegler3

Just an addition: sde.gdb_util.next_rowid('gis', 'table_name') creates data in a data type named st_geometry(). Wondering if that would also be an issue.

allan

Do you see anything in the inner works of Editor that might not like this approach?

Yes - that is an SQL injection attack waiting to happen. You should never execute text sent from the client-side as an SQL query. Parameters should be bound (i.e. escaped by the SQL engine), but adding a whole string to the query is bad news.

Assuming you are using the Editor server-side libraries (?) you wouldn't be able to do that anyway, as the query is dynamically created based on the configuration and the data submitted.

If I understand the query correctly, you want to set the value of one column for the inserted row to be the next in a sequence? If so, I'd very much recommend using a trigger to do that.

Allan

aziegler3

You are right, about the SQL injection possibilities, but the whole SDE library is solid and they have tight controls on it.
I was using the next_row as an example, but there are three similar columns that do more complex things that just the next in the sequence, so I was trying to avoid the use of triggers, which i am not that good at.

You are saying that the query is dynamically created based on the configuration. But I should be able to add items to data[], in something like:

editor
    .on( 'preSubmit', function( e, d, action ) {
      some logic to add to data[0]
}):

or this just won't fly?

allan

SDE = Secure Data Environment?

If you are using the Node.js, PHP or .NET server-side libraries for Editor, then yes, the query is created dynamically at the server-side. Are you?

You can certainly modify the data to send to the server using preSubmit, but if you need to write SQL statements or commands, you have to do that at the server-side.

Allan

aziegler3

SDE = Spatial Data Engine. The software that we us to store spatial data in Oracle.

Yes. I am using the the PHP server-side library.

Thank you for the pointer toward write SQL statements or commands, you have to do that at the server-side. I haven't done it before. Any docs or examples that you might suggest?

allan

Thanks for the clarification.

If you could explain what you are looking to do, then hopefully I can help. Most likely you'll need to use some server-side events.

The modifying field values example on that page might be of particular issue. In the sequence case, you could get the next in the sequence and write that as the value. A trigger would be better for that, but doing it in PHP is an option.

Allan

aziegler3

Allan, thank you for all our help. Your insights are really good and I appreciate them.

The SQL string for an Create would have, among other bunch of tabular data, three fields that are calls to SDE libraries. Each one is one item that holds spatial information in the sql query that creates a new entry in the table, and each one belongs to a particular column in the table.

sde.gdb_util.next_rowid('gis', 'table_name ')    
sde.gdb_util.next_globalid(),
(sde.st_geometry('point (".$longitude." ".$latitude.")', 4326))

So I am thinking that if I modify the field value, as you suggest here, I should be able to make the call to SDE, and replace the value before the new row gets created. Makes sense to you?

allan

The sde functions are in SQL are they? If you were to use field()->setValue(...) that will be bound / escaped, and therefore not run as a function (not doing that would be a major security risk!).

If you need to set a column value based on SQL function calls when creating / editing a row, there is little question in my mind that you should use a trigger.

That said, you could query the database with the data submitted from the client, get back the answer, and then write that to the field's value.

To do that call $editor->db()->resource() which will get you the PDO instance so you can work directly with the database.

I will say I recognise this as a bit of a pain point - it would be useful in the Editor PHP libraries to work with SQL functions for setting column values, and that is something that I plan to improve in future.

Allan