| Author |
 Topic  |
|
|
oap
Yak Posting Veteran
USA
59 Posts |
 Posted - 01/29/2013 : 16:27:02
|
Hi there!
I have three SQL statements that occur consecutively. What happens is a person will leave a comment for a photo and if the author of the photo has enabled email notifications, a message is sent out to them alerting them of a new comment.
There is an easier way to do this, I just don't know how.
recid = 333 (example: photo is numbered 333)
strSQL = "SELECT * FROM photolist WHERE id = " & recid
set objRS = objConn.Execute(strSQL)
vdir = objrs("directory")
^ vdir is a value indicating what location number the photo is for. every location is numbered
strSQL = "SELECT * FROM sitelist WHERE ID = " & vdir
set objRS = objConn.Execute(strSQL)
whomade = objrs("createdby")
^ fetch the name of the person that created the entry
strSQL = "SELECT email,notifycmnt FROM userlist WHERE username = '" & whomade & "'"
set objRS = objConn.Execute(strSQL)
^ fetch email address and notify flag from the user that made entry
and then if notifycmnt = "yes" then send a message
So lets say that photo 333 receives a comment, it will pull the directory first, which let's say would be "My CN Tower photos".
Then it goes back to 'sitelist' to retrieve from the table where "My CN Tower photos" is, the "createdby" field.
Let's say that 'createdby' for 'My CN Tower photos" is "George1"
Then it gets email and notifycmnt from the user database for "George1" to grab his email address and check if the notify-new-comment field says "yes"
Can you help? 
|
Edited by - oap on 01/29/2013 16:27:52
|
|
|
James K
Flowing Fount of Yak Knowledge
1515 Posts |
Posted - 01/29/2013 : 17:03:54
|
You can join all three tables together - like shown below (I am showing the T-SQL; you would concatenate with the recid to produce the query string shown below)SELECT
u. email,
u.notifycmnt
FROM
photolist p
INNER JOIN sitelist s ON s.ID = p.directory
INNER JOIN userlist u ON u.username = s.createdby
WHERE
p.id = 333; |
 |
|
|
oap
Yak Posting Veteran
USA
59 Posts |
Posted - 01/29/2013 : 17:23:00
|
Thanks for the response. I tested it under MS SQL and it is hit and miss. It worked a few times but now I receive no rows returned even though the fields are valid.
Ironically where I put in a value for p.id that doesn't exist (a deleted location) it DOES return an email address, I just don't know whose.
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1515 Posts |
Posted - 01/29/2013 : 18:09:18
|
That sounds like the join conditions may not be correct. Does directory column on photolist table correspond to the ID column on sitelist table? And does the createdby column on sitelist correspond to username table on userlist table?
Another thing to consider is whether every photolist has a non-null directory value and whether every sitelist has a non-null createdby value. If that is not the case, you would get no row for that combination.
Take one example (one row) where it misses and examine the values in each of the tables. If you can post some sample data that would help. |
|
|
|
oap
Yak Posting Veteran
USA
59 Posts |
Posted - 01/29/2013 : 19:03:14
|
Oh man. I apologize, I was using the location # instead of the photo #.
Your code worked perfectly! I feel like I should be paying money each time someone posts a solution, it saves so much time.
Thank you! |
|
|
|
LoztInSpace
Aged Yak Warrior
876 Posts |
Posted - 01/29/2013 : 21:18:17
|
I'll pay *you* money if you stop using concatenated values and use parameterised queries instead!
My 2 cents (which is what I'll pay you :) ). You can pay it back when you've learned about SQL injection that stems from not using parameters. |
|
|
|
James K
Flowing Fount of Yak Knowledge
1515 Posts |
Posted - 01/30/2013 : 07:39:44
|
Even though LoztInSpace is saying what he is saying in a light-hearted manner, the threat of SQL injection is serious and real. For example, even though you are expecting someone to provide a number such as 333 for the recid, a malicious user can send something like shown below - where the "create havoc" can be pretty much anything.333; CREATE HAVOC ON OAPS_DATABASE; And that is only the beginning. Hence the advice against concatenated values. If you use a parameterized query, ideally a stored procedure, they would not be able to do such strange stuff to your database. |
|
|
|
oap
Yak Posting Veteran
USA
59 Posts |
Posted - 01/30/2013 : 09:12:29
|
I understand.
SQL is not my best area of expertise but I do use this code:
val = left(request.querystring("value"),5)
val = replace(val,"'","")
So if someone is going to perform SQL injection they'd have to do it in 5 characters or less and without the use of an apostrophe.
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1515 Posts |
Posted - 01/30/2013 : 09:31:30
|
| Great! I am no expert on SQL injection, but that seems safe enough. |
|
|
| |
Topic |
|
Combining 3 select statements
