SQL statements embedded in code? 2

[Griffyn]

Hi all,

I've set up libraries that have SQL embedded in the Delphi code to handle modifying data in our main application database. The database is PowerFlex (or DataFlex) and while it supports views, it doesn't support things like stored procedures. So my libraries are set up as flexible core functions to do things like CreateTransaction, DeleteTransaction, etc. so that my programs never use SQL at all, and just use these high-level library functions.

But - that still leaves the SQL as strings in my code, and presumably in the final .exe, and from what I've read, this is bad. But nowhere have I found suggestion of a better way, aside from stored procedures.

What's the best practice here?

 

[whosrdaddy]

I don't see why it would be bad.
The only thing you need to take into account is SQL injection (ie concatenating your statement with actual values).
A hard & fast rule is to always use parameters in code.

/Daddy

-----------------------------------------------------
Helping people is my job...

 

[DjangMan]

Another option is to store the SQL commands with a SQLite database that is encrypted. Prying eyes won't be able to see the SQL and if you have to update the SQL you might be able to simply update the SQLite database vs recompiling.

I second Daddy's direction on using parameters where possible.

 

[Griffyn]

Parameters all the way, agreed.

I couldn't really understand why it's bad, although if I were distributing my apps to unknown persons, I'd take steps to encrypt like DjangMan suggests, but then I can't really think what an external app would be doing with SQL anyway. It's all server-side stuff.

Thanks for your thoughts. You've put my mind at ease.

 

[Glenn9999]

but then I can't really think what an external app would be doing with SQL anyway. It's all server-side stuff.

The SQL statement is how you interact with the database. Send SQL statement, retrieve results. So anything that gets data out of the database, no matter where it is located, would require your app to ship the SQL to do it. So what you wrote is very true:

that still leaves the SQL as strings in my code, and presumably in the final .exe