SqlCommand class has been added to my ClarionClasses git repo

code Snippets
1

This is a copy of the current, very basic, documentation:

Sort of based on the .NET version, this is a simple class to execute a command either with a single value reponse ExecuteReader or just simple execute ExecuteNonQuery.

Example usage:

Sql SqlCommand
  CODE

  Sql.Init(GLO:FILE:TurboSQLOptions, GLO:FILE:OwnerString)
  Sql.Str('CALL MyStoredProc(123,''Somestring'')')

  Message('Result=' & Sql.ExecuteReader())

SqlCommand.inc

SqlCommand.clw

2 Likes
2

Hey Brahn,

So would this allow me to run "exec " type sql commands from clarion code?

3

Yes but no. You still need to translate them to the Clarion (ODBC) syntax for calling stored procedures.

e.g. In SSMS you would EXEC MyStoredProc 123, 'Somestring'

Using the SqlCommand class you would need two lines:

Sql.Str('CALL MyStoredProc(123,''Somestring'')')
Sql.ExecuteNonQuery() ! or ExecuteReader() as appropriate...

I suppose an optional method parameter could be added so it is just a one liner:

Sql.ExecuteNonQuery('CALL MyStoredProc(123,''Somestring'')')

Perhaps you could put something in to parse EXEC MyStoredProc 123, 'Somestring' internally and translate it to the appropriate CALL syntax but that would get special pretty quick.

4

Thanks Brahn! Fantastic.

1 Like
5

It would be better, if you check SQL syntax for possible injections.

1 Like
6

Yes indeed! Although I would argue that the responsibility for that would be on the user of the class. It might be very hard to generically/automatically guard against injection with the correct context of the calling code. On the other hand, if you look at the the sql profiler to see how Clarion eventually hands the code over to SQL you should see that it is parameterised which I believe negates a lot of injection concerns.

I would be very interested to see some ideas on how to defend against injection attacks though. Any standard practices you can suggest? Anyone else got some ideas/thoughts? Go!

7

This one is very very simple code. It should has more enhancements.

Loc:Result=Level:Benign

IF (INSTRING('DELETE ',UPPER(Par:SQLString),1,1)>0 OR INSTRING('EXEC ',UPPER(Par:SQLString),1,1)>0 OR INSTRING('ALTER ',UPPER(Par:SQLString),1,1)>0) THEN
    vzDebug(15,'SQLCheck: Very dangerous query! '&Par:SQLString)
    Loc:Result=Level:Notify
END

IF  INSTRING('DROP ',UPPER(Par:SQLString),1,1)>0 AND ( |
    INSTRING('TABLE ',UPPER(Par:SQLString),1,1)>0 OR | 
    INSTRING('DATABASE ',UPPER(Par:SQLString),1,1)>0 OR |
    INSTRING('LOGIN ',UPPER(Par:SQLString),1,1)>0 OR |
    INSTRING('SCHEMA ',UPPER(Par:SQLString),1,1)>0 OR |
    INSTRING('INDEX ',UPPER(Par:SQLString),1,1)>0 OR |
    INSTRING('VIEW ',UPPER(Par:SQLString),1,1)>0 OR |
    INSTRING('TRIGGER ',UPPER(Par:SQLString),1,1)>0 OR |
    INSTRING('PROCEDURE ',UPPER(Par:SQLString),1,1)>0 OR |
    INSTRING('FUNCTION ',UPPER(Par:SQLString),1,1)>0) THEN
    vzDebug(15,'SQLCheck: Drop - Command rejected! '&Par:SQLString)
    Loc:Result=Level:Fatal
END

IF (INSTRING('TRUNCATE ',UPPER(Par:SQLString),1,1)>0) THEN
    vzDebug(14,'SQLCheck: Command rejected! '&Par:SQLString)
    Loc:Result=Level:Fatal
END

IF INSTRING('SYS.',UPPER(Par:SQLString),1,1)>0 THEN
    vzDebug(15,'SQLCheck: Very dangerous query! '&Par:SQLString)
END

RETURN(Loc:Result)
1 Like
8

I guess you would want to differentiate between when you execute any SQL code that is connected to anything that a user inputs vs something compiled into the program. A lot of the time these strings are exactly what I want to use the SqlCommand class for :slight_smile:

As a side note, I was working on this class some more today and added functionality to read back more than one result. I am still road testing it but the idea goes something like this:

Sql.Init(GLO:FILE:TurboSQLOptions, GLO:FILE:OwnerString)
Sql.Open()
Sql.Str('SELECT Id FROM MyTable')
Sql.ExecuteReader()
LOOP
  myTableId = Sql.Read()
  IF myTableId = 0
    BREAK
  END
  Message('MyTable.Id = ' & myTableId)
END
Sql.Close()

Perhaps this could also be a better way:

Sql.Init(GLO:FILE:TurboSQLOptions, GLO:FILE:OwnerString)
Sql.Open()
Sql.Str('SELECT Id FROM MyTable')
Sql.ExecuteReader()
LOOP UNTIL Sql.Read() <> Level:Benign
  Message('MyTable.Id = ' & Sql.LastResponse())
END
Sql.Close()
1 Like
9

Nice work. I liked second one too. (Init parameters may switch because Owner String will be in all case but Options may not)
How do you plan to return results. Because I may want to return more than 1 (currently 15) results in my prodecure.
Also users may need line numbered results. (250th row of results. MSSQL supports that usage with ROW_NUMBER())

10

Yes, that was my next though also.

I am not sure yet what would be the best way. It could be quite generic but would be very reliant on Clarions inbuilt data conversions which may or may not be a good thing. It’s the sort of thing that can “just work” until it doesn’t and then you are in trouble :slight_smile:

What I would like to do is provide a built in “as-is” generic collection of some kind that could considered good enough for basic values but implemented using an interface or something so that you can provide your own implementation should you need more control over data types.

1 Like
11

If there is any interest, I have a more recent version of this class that is thread safe plus a few other changes.
The only problem is it needs the dynamic file driver.

Let me know if you are interested and I can share privately or, if there is enough interest I will look at posting it too (just not sure I am ready to support DFD questions :slight_smile: )

1 Like
12

Ok, due to popular demand I have uploaded the DFD version! :slight_smile:

Other changes to the repo:

  • RED files to support C11
  • A solution at the root of the project that contains all the example so you can compile and test them in one go.
  • New example for SqlCommand demonstrating a couple of features
1 Like