Link to home
Create AccountLog in
Avatar of sej69
sej69

asked on

Writing a query to a database (MSACCESS)

I'm linking to external DBs through my app.  In doing so I have to write queries and save them for later recall.  I'm only running on a couple of hours of sleep when I tried this.  Then I realized that this is what an injecttion attack would consist of and why I was getting weird messages about my WHERE clause.  I'm not concerned about injection attacks on this C# app; it's internal only.

What's happening is I have a full query:
SELECT * from tblMyTable
WHERE tblMyTable.index = 1;

If I try to save that query to the MS Access MDB it throws an error because the actual query is run as it's saved; a query within a query.

The field that I'm attempting to write into is of type MEMO.  I can write anything else into it, but again, once I attempt to write SQL code it bombs.

I've tossed around the idea of hashing the SQL code before I write it but it would be nice if I could read it right in the table without decoding it.

Has anyone run into this before or have any suggestions on what I can do to get around it?

Thanks!
Avatar of AielloJ
AielloJ
Flag of United States of America image

sej69:

Essentially, you're trying to saave a string, not execute a query.  Have you tried placing it in quotes, shich will treat it like a string, before writing it to the database?

Regards,

AielloJ
Avatar of Den_HBR
Den_HBR

Like AielloJ said, I should be possible if you place quotes arround your query.

I that doesn't work,maybe you could add it in 2 steps.

First step, save the reversed query (
Insert tblSQLquery(SQL)VALUES(';1 = xedni.elbaTyMlbt EREHW elbaTyMlbt morf * TCELES')

Second step, update the record.
Update tblSQLquery SET SQL = STRREVERSE(SQL)
ASKER CERTIFIED SOLUTION
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Besides this, there are mechanisms dedicated to predefined sql, that is stored procedures, views or in access the different sort of queries. You actually will not use such a mechanism of storing sql code in a table.

Bye, Olaf.
Avatar of sej69

ASKER

Hey Olaf, that sounds interesting.  I dont think I'll have a chance to play with this today, but I'll look at it latest tomorrow.

This interface is for me to quickly link to the external database.  The C# app runs on a protected computer with very limited access so I'm not concerned about injection on this one.  Had it been a web app then I wouldn't consider this.
I've not used C# at all, but I would think that another alternative would be to open a recordset, add a new record, and then set the value of the field you want to the value of the string.  In DAO, this would look like:

    strSQL = "SELECT [MemoFieldName] FROM tblYourTable WHERE FALSE"
    Set rs = Currentdb.OpenRecordset(strSQL, , dbFailOnError)
    rs.AddNew
    rs!MemoFieldName = 'SELECT * from tblMyTable WHERE tblMyTable.index = 1'
    rs.Update
    rs.close
    set rs = nothing
Avatar of sej69

ASKER

Worked like a charm, I used an UPDATE table SET myMemoFieldName = @sql

Thanks again, Olaf!