Re: Proper parens in OR searches

[Michael Stassen]

Scott Haneda wrote:
This SQL in mysql 4 is too aggressive and seems to ignore my 30 day date
range, if I remove the AND it finds the date range I want, putting it back
in does not.  I have tried various ways of wrapping ( and ) in the SELECT to
no avail.
Because AND takes precedence over OR.
I am trying to get all records where the subject is one of the 3 subjects
but also falls within 30 days old
SELECT id, name FROM listmail
WHERE date_time > DATE_SUB(NOW(), INTERVAL 30 DAY)
AND  subject = "semaphore" ||
subject = "Re: semaphore" ||
subject = "Re:semaphore"
ORDER BY id ASC LIMIT 60
so this is (date_time > ... AND subject = ...)
        OR (subject=...)
        OR (subject=...)
I expect the date range was respected for the first subject match but not 
the other two.

For what its worth, this does what I want for me, but I am concerned this
will allow SQL injection in the subject as the emails come in.  running
mysqlescape (or whatever it is called) seems to make mysql cranky.
SELECT id, name FROM listmail WHERE date_time > DATE_SUB(NOW(), INTERVAL 30
DAY) AND  subject IN ('semaphore', 'Re: semaphore', 'Re:semaphore') ORDER BY
id ASC LIMIT 60
With parens:
  SELECT id, name FROM listmail
  WHERE date_time > DATE_SUB(NOW(), INTERVAL 30 DAY)
  AND (subject = 'semaphore'
    OR subject = 'Re: semaphore'
    OR subject = 'Re:semaphore')
  ORDER BY id ASC LIMIT 60
Without parens:
  SELECT id, name FROM listmail
  WHERE date_time > DATE_SUB(NOW(), INTERVAL 30 DAY)
  AND subject IN ('semaphore', 'Re: semaphore', 'Re:semaphore')
  ORDER BY id ASC LIMIT 60
These two are perfectly equivalent.  The latter is, of course, the same 
query as in your post.

Why do you expect these 2 queries to be different with respect to SQL 
injection?  It seems to me you need to validate your input either way. 
Perhaps if you reminded us what language you're using, showed us the insert, 
and told us what you mean by making mysql "cranky", someone could help you 
solve that problem.

Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]