TOPIC: String Escape function for queries ?
#1716
String Escape function for queries ? 8 Months, 2 Weeks ago
I couldn't find a function to Escape strings to be included on a query, there is one or how this is managed in suneido to prevent the equivalent of sql injection ?

I found the function QueryOutput("testupdate", Record(id:1, percentage: 100)), but it seems to be used only to insertions of new records.

Could be useful if maybe the above function could be extended to updates and handle the by roviding the key as another parameter parameter like:

QueryOutput("testupdate", key: 'id', Record(id:1, percentage: 100))

Or something like that.

I've tried with record.Update but it doesn't seem to be useful whe we want only to update certain fields on table that has other fields changed by other processes.

For example a table for "products_to_sell" where there is a field "onhand" to register the actual existing quantity of a product this field is update by invoices only and can change frequently and I don't want to make it hard to update other "products_to_sell" fields.

With the actual suneidos queries and record updates there is a double round trip (read and then write) that can be fine for a table not updated so often.

Normally I use a field 'record_version' on tables that is increased by each update so I send a update query with a where clause to check if this field remains the same in which case the update succeed with only one call to database.

In the above case I update the "onhand" field from invoices without changing "record_version" field (inside a transaction) so the "products_to_sell" is easily update on other fields (unless other user updates it too, in that case the update will fail).
 
 
#1717
Related to the above ! 8 Months, 2 Weeks ago
When working with client server where is the rules executed client or server ?

I hope on server because will be hard to enforce rules otherwise !

A code bellow from Access1 is executed on client isn't it?

Code:


save_update()
{
Transaction(update:)
{ |t|
x = t.Query1(.query)
if (not OverwriteRecord?(.original_record, x, .query_columns, .Window.Hwnd))
return false
Assert(x.Update(.Data.Get()))
return true
}
}

 
 
#1718
Re:String Escape function for queries ? 8 Months, 2 Weeks ago
If you use Display you should be safe from SQL injection type attacks.

x = QueryFirst("customers where city = " $ Display(city))

Or with the latest exes you can do:

x = QueryFirst("customers", city: city)

You can use an update query to update records without reading them first, for example:

QueryDo("update customers where city = " $ Display(city) $
" set status = " $ Display(newstatus))

It would be easy to make a function that would build this kind of update query string.
 
 
andrew
 
#1719
Re:String Escape function for queries ? 8 Months, 2 Weeks ago
Yes, that Access1 code will be executed on the client.

Rules may be executed on the client or on the server, depending where the rule field is accessed.

If you have a "where" on a rule, then it will be executed on the server.

Rules are "lazy" i.e. executed on demand, so where they will be executed depends on where they are accessed.

Triggers are executed on the server.
 
 
andrew
 
#1720
Re:String Escape function for queries ? 8 Months, 2 Weeks ago
Thanks for your help, I suggest add a mention about this on users manual on database section and/or a fake entry to an Escape function that points to Display.