Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 10 of 10 total |
SQL "Parse" Function |
Mon, Dec 1 2008 2:00 PM | Permanent Link |
KM | In my application, I want to be able to give users the flexibility of SQL-based queries but only through the use of the SELECT statement. I don't
want them to be able to use ALTER, DROP, etc. I looked at the events a TQuery fires, in hopes that I could try to abort any 'destructive' type operations. But it didn't look like I could get 100% coverage doing that. Does DBISAM have a way to 'pre parse' an SQL input string? If there were a pre-parse capability, I could ask DBISAM to parse an input statement and then I could remove any non-SELECT statements before executing the SQL. Any input / suggestions would be appreciated. With thanks, KM |
Mon, Dec 1 2008 3:16 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | << In my application, I want to be able to give users the flexibility of SQL-based queries but only through the use of the SELECT statement. I don't want them to be able to use ALTER, DROP, etc. I looked at the events a TQuery fires, in hopes that I could try to abort any 'destructive' type operations. But it didn't look like I could get 100% coverage doing that. Does DBISAM have a way to 'pre parse' an SQL input string? If there were a pre-parse capability, I could ask DBISAM to parse an input statement and then I could remove any non-SELECT statements before executing the SQL. >> Are you using 4.x ? If so, then just write some triggers for inserts, updates, and deletes that raise exceptions when the user isn't the "correct" user. You can find out more information here: http://www.elevatesoft.com/manual?action=mantopic&id=dbisam4&product=d&version=7&category=1&topic=8 under "Triggers". -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Dec 1 2008 3:50 PM | Permanent Link |
"Robert" | "KM" <info@microlynxsystems.com> wrote in message news:4B9A3737-E8C1-4411-9CD6-F626D2B02F32@news.elevatesoft.com... > In my application, I want to be able to give users the flexibility of > SQL-based queries but only through the use of the SELECT statement. I > don't > want them to be able to use ALTER, DROP, etc. > > I looked at the events a TQuery fires, in hopes that I could try to abort > any 'destructive' type operations. But it didn't look like I could get > 100% > coverage doing that. > > Does DBISAM have a way to 'pre parse' an SQL input string? If there were > a pre-parse capability, I could ask DBISAM to parse an input > statement and then I could remove any non-SELECT statements before > executing the SQL. > > Any input / suggestions would be appreciated. Seems to me that if the first word in query.text is not SELECT, you don't want the whole query. Or if you find any of the other commands (;UPDATE ALTER etc).You might need a bit more logic if you allow comments in the query, but nothing that can't be done with a few lines of code. Robert > > With thanks, > KM > |
Mon, Dec 1 2008 3:55 PM | Permanent Link |
"Robert" | "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message news:4299A522-1356-4F75-935C-040BC492F865@news.elevatesoft.com... > > << In my application, I want to be able to give users the flexibility of > SQL-based queries but only through the use of the SELECT statement. I > don't want them to be able to use ALTER, DROP, etc. > > I looked at the events a TQuery fires, in hopes that I could try to abort > any 'destructive' type operations. But it didn't look like I could get > 100% coverage doing that. > > Does DBISAM have a way to 'pre parse' an SQL input string? If there were > a pre-parse capability, I could ask DBISAM to parse an input statement and > then I could remove any non-SELECT statements before executing the SQL. >> > > Are you using 4.x ? If so, then just write some triggers for inserts, > updates, and deletes that raise exceptions when the user isn't the > "correct" user. You can find out more information here: > > http://www.elevatesoft.com/manual?action=mantopic&id=dbisam4&product=d&version=7&category=1&topic=8 > > under "Triggers". Tim, aren't triggers table specific? Have not used them, so this is just from reading the docs. If they are, they would not do the trick, unless you put a ton of code in. The user SQL could conceivably modify ANY table, or for that matter drop it altogether. Robert |
Mon, Dec 1 2008 5:47 PM | Permanent Link |
KM | All,
Thanks for your feedback. I think I might just scan the SQL input string for any 'forbidden words' - seems like the easiest thing to do. KM |
Tue, Dec 2 2008 1:32 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | KM
My recommendation would be to use one of the query builder tools out there so the average user doesn't even get the chance of typing UPDATE. If you want you can have a copy of my homebrewed one for V4. Roy Lambert |
Tue, Dec 2 2008 2:54 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Robert,
<< Tim, aren't triggers table specific? >> Not in DBISAM, no. The table name is passed into the trigger. << The user SQL could conceivably modify ANY table, or for that matter drop it altogether. >> DROPs are an issue, yes, but the triggers will cover any updates. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Dec 2 2008 3:50 PM | Permanent Link |
"Robert" | "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message news:7D2CDD10-A4E4-4544-9B28-D20BED88CDFC@news.elevatesoft.com... > Robert, > > << Tim, aren't triggers table specific? >> > > Not in DBISAM, no. The table name is passed into the trigger. > Let me try the question a different way. I have to specifically ask for the trigger to be applied to this table, true? IOW I can't have an update trigger at the database level, that gets fired on ANY table being updated. Related question: do you have in Elevate DB security at the user level such that I can specify a user never gets to do updates? Robert |
Wed, Dec 3 2008 4:09 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Robert
>Let me try the question a different way. I have to specifically ask for the >trigger to be applied to this table, true? IOW I can't have an update >trigger at the database level, that gets fired on ANY table being updated. Its more that you have to work out in the trigger which tables to ignore. From the pdf manual procedure TMyForm.EngineBeforeDeleteTrigger(Sender: TObject; TriggerSession: TDBISAMSession; TriggerDatabase: TDBISAMDatabase; const TableName: String; CurrentRecord: TDBISAMRecord); So my understanding (from a while ago now) is any time there's a delete this gets fired and you have to work out in the trigger if you want to do anything. >Related question: do you have in Elevate DB security at the user level such >that I can specify a user never gets to do updates? YUP Look at a recent thread "How i can to create a readonly user ?" in elevatedb.general Roy Lambert [Team Elevate] |
Wed, Dec 3 2008 8:00 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Robert,
<< Let me try the question a different way. I have to specifically ask for the trigger to be applied to this table, true? >> No, the triggers are global in DBISAM by their very nature. << IOW I can't have an update trigger at the database level, that gets fired on ANY table being updated. >> Yes, you can. In fact that's all you get in DBISAM. << Related question: do you have in Elevate DB security at the user level such that I can specify a user never gets to do updates? >> Yes, see here: http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=0&topic=8 -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Monday, April 29, 2024 at 05:23 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |