Is there anything in the Engine.QuotedSQLString to protect against SQL injection
attacks? Is there a similar function in the Engine that should be used instead or
as well? If not, what is recommended?

/Matthew Jones/

Matthew

>Is there anything in the Engine.QuotedSQLString to protect against SQL injection
>attacks? Is there a similar function in the Engine that should be used instead or
>as well? If not, what is recommended?

From my older manual

When dynamically building SQL statements that contain literal string constants, you can use the TDBISAMEngine
QuotedSQLStr method to properly format and escape any embedded single quotes or non-printable characters in the
string.

so unless its changed dramatically (doubtful) I don't think it will do anything to prevent sql injection attacks, and I can't see anyway it could since you may be legitimately passing a DROP, INSERT, UPDATE etc command to it.

You're best approach is to provide the end users with a query generator that doesn't have the nasty words in its vocabulary or only use parameterised queries where you can make sure verbs are as you want.. If you can't do that then you'll have to scan and de-sensitise the input yourself.

Roy Lambert [Team Elevate]

I think I'd better have a ponder on this. I hate re-inventing security wheels - I'm
likely to miss some basic thing. Even switching to parameters, which makes the
immediate use safe, means dodgy SQL can be stored to come back and bite later.

If anyone has a routine that already does sanitisation, do please let me know.

/Matthew Jones/

Matthew,

<< I think I'd better have a ponder on this. I hate re-inventing security
wheels - I'm likely to miss some basic thing. Even switching to parameters,
which makes the immediate use safe, means dodgy SQL can be stored to come
back and bite later.  >>

Could you elaborate on what you're thinking of here ?  I can't imagine any
scenario under which parameters containing SQL could be later executed after
they are stored unless you're allowing users to enter in SQL directly and
execute it.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com

Just the usual problem. You allow a human to enter data (or web page) which you
then store in the database.

Thus user enters "hello;DROP MYTABLE" (or some clever thing).
You store it using
INSERT INTO MyTable (MyValue) VALUES (:HumanText)
which is of course completely safe.

So later you get the data out again
SELECT MyValue FROM MYTABLE
and assign it to a variable
szMyValue := MyTable.FieldByName('MyValue').AsString;

Now, just need to find the matching record:
Query.SQL.Text := 'SELECT * FROM AnotherTable WHERE MyField = ' + szMyValue;

The problem is just the usual issue where you let slip one time - if the data is in
there, then it can be dangerous, long after the data got in. Inject the code, then
initiate an action that uses it in plain.

Obviously one way is to sanitise it at any point where you run a check to ensure
that there is no way dangerous code can get in. It was that function that I seek,
in that if I were do to my own, I'd miss things. I expect there are common routines
on the web. I was wondering how other DBISAM/ElevateDB users handle this.

/Matthew Jones/

Matthew


If that is the type of scenario you expect I think you're worrying a bit unnecessarily. I just tried this in ElevateDB in my general try all the crap out on it database

select * from test where _varchar = '456;drop table ddd'

All that happened was no rows were returned. This particular scenario is perfectly safe since the test string has to be wrapped in quotes and its just a string. What may give you trouble would be something like:

select * from test where _integer = 456;drop table ddd

but the query component you use would have to execute multiple statements separated by ; ElevateDB doesn't so I just get an error. I could build a script and that still wouldn't work since I'd have to use an EXECUTE IMMEDIATE call at which point I'd get an error again. I think it would work in DBISAM but I don't have a junk database to test.

Even with that query if you use parameters you're safe again since

456;drop table ddd

is very unlikely to be considered an integer and it would be rejected.

So if all users can do is enter bits for the WHERE clause then use parameters and you're safe. Allow them to enter full SQL commands and you (and they) deserve everything they get

Roy Lambert [Team Elevate]

> select * from test where _varchar = '456;drop table ddd'
>
> All that happened was no rows were returned. This particular
> scenario is perfectly safe since the test string has to be wrapped
> in quotes and its just a string.

I think the key is that you then enter a quote in the field, to terminate it nicely.
As you say, it does depend on the use of a component that runs multiple, but I
certainly do ExecSQL or whatever it is.

I guess though that since I'd always use Engine.QuotedSQLString for my data, the
key question becomes:
Does Engine.QuotedSQLString escape quotes in the string parameter?

If it escapes them, so that a user entered ' would become '' and interpreted by the
parser as a single quote again, then all would be well most likely.

/Matthew Jones/

Matthew

>I think the key is that you then enter a quote in the field, to terminate it nicely.
>As you say, it does depend on the use of a component that runs multiple, but I
>certainly do ExecSQL or whatever it is.

BUT then you have to store and use exactly as typed by the user ie assume they entered quotes around text.

Again though, if you use parameters, you would be supplying something that wasn't a string, number or whatever and the parser would throw an error.

>I guess though that since I'd always use Engine.QuotedSQLString for my data, the
>key question becomes:
>Does Engine.QuotedSQLString escape quotes in the string parameter?

AKAIK its just a wrapper around QuotedStr so yes.

Roy Lambert [Team Elevate]

> BUT then you have to store and use exactly as typed by the user ie
> assume they entered quotes around text.

The key is that you cannot assume anything. But it does look like always using
QuotedStr, or Parameters, will make it safe.

/Matthew Jones/

Matthew,

<< Now, just need to find the matching record:
Query.SQL.Text := 'SELECT * FROM AnotherTable WHERE MyField = ' +
szMyValue; >>

Yes, but you're specifically *not* using parameters here.  If you use
parameters for all user-supplied data then no problem, period.

Tim Young
Elevate Software
www.elevatesoft.com