Using SQL from Access to ASP
-
Hi,
I am creating an ASP page that uses backend access database.
I am trying to pull the correct data from a previous page depending on the id of that link.
I am trying to use 2 conditions. First get the pageid which is set to EatingDisorders. the second is the id of the page.
When i add the AND condition it doesn't work.
strSQL2 = "SELECT qryEatingDisorder.* FROM qryEatingDisorder WHERE PageID = '" & lngRecordNo & "'" and EatingID = '2'"
Any any help would be great.
Regards
Ritesh
-
Ritesh,
It's looks as though you have missed out a &
strSQL2 = "SELECT qryEatingDisorder.* FROM qryEatingDisorder WHERE PageID = '" & lngRecordNo & "'" & "and EatingID = '2'"
Should do the trick.
Rodney.
-
Rodney,
It still does not work I get a type mismatch error. What I really want to do is to have a variable instead of the 2.
Dim pid
pid = Request.QueryString ("ID")
the variable that is at the start. it reads the querystring and then I want this to go into the and condition. eg.
& "and EatingID = 'pid' "
Help- the pid is suppose to be a variable like the lngRecordNo variable.
-
& "and EatingID = '" & pid & "' "
Also look up Parameterized Queries in MSDN Library. The method of appending strings like this opens you up to SQL Injection big-time. Someone could pass a value of:
';DELETE FROM table; --
In pid and mess up your database big time.
-
Hi Mike, do you mean you'd put
pid = "DELETE FROM table" ?
-
Well, more like:
pid="2;DELETE FROM table;"
Put that into your SQL, and remember that semicolons terminate SQL statements. Some yahoo has just passed a statement into your system that retrieves your data, and then promptly deletes a bunch of data. That's why most books and help files stress validating your data before passing it to the query.
On another note, since you're getting a type mismatch error, is EatingID a text data type? If not, you don't need the quotes around the 2, or whatever value you're passing.
-
No and No.
I mean what I said: pid = "';DELETE FROM table; --"
Let's assume lngRecordNo = "2", for the sake of argument. If you look at the code, this will generate the following SQL statements:
SELECT qryEatingDisorder.*
FROM qryEatingDisorder
WHERE PageID = '2'
and EatingID = '';
DELETE FROM table;
--'
Marshall's version generates:
SELECT qryEatingDisorder.*
FROM qryEatingDisorder
WHERE PageID = '2'
and EatingID = '2;DELETE FROM table;'"
This will presumably just return no records. The DELETE statement will NOT be executed in this version.
Grovelli's version generates the following SQL:
SELECT qryEatingDisorder.*
FROM qryEatingDisorder
WHERE PageID = '2'
and EatingID = 'DELETE FROM table'"
Which again will presumably just return no records. The keys to SQL injection are the closing apostrophe at the beginning of pid, the semicolon which separates multiple SQL commands, and the trailing "--" which turns anything after the DELETE statement into a comment (i.e., the trailing apostrophe in the statement is ignored to prevent an exception from occurring).
I focused in on pid because it is passed directly from the Address line of the browser. The lngRecordNo could also pose the same issue if a user were able to set this value. There are several articles on the subject of SQL injection here at SSC. I know cause I wrote one of them.
-
Oh, right. In my head, I'd already pulled out the apostrophe, because it looked like he was trying to use integer values. So, there should have been an apostrophe after the 2.
-
He might have meant it to be an integer value, but since he's using ASP (not ASP.NET) he's probably using the Variant data type. So there's no strong type checking and someone could slip him a string when he's expecting a number
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply