0

I compare email addresses in PHP from MySQL database:

$query
    ->select(array('a.dtstart','a.cal_id','b.name','a.email'))
    ->from('#__pbbooking_events AS a')
    ->join('INNER', '#__pbbooking_cals AS b ON (a.cal_id = b.id)')
    ->where('a.email =  ' . $user->email)
    ->where(' a.dtstart>'. $query->currentTimestamp()) 
    ->order('a.dtstart ASC');

$user just has information of the currently logged user.

I get this error

YOU HAVE AN ERROR IN YOUR SQL SYNTAX; CHECK THE MANUAL THAT CORRESPONDS TO YOUR MARIADB SERVER VERSION FOR THE RIGHT SYNTAX TO USE NEAR @RET.RU AND A.DTSTART>CURRENT_TIMESTAMP() ORDER BY A.DTSTART ASC AT LINE 4

If I remove the where clause that compares emails, then the query runs without error.

I have read somewhere that the problem is the @ sign but how can I make this comparison then?

Toby Speight
  • 27,591
  • 48
  • 66
  • 103
Olga
  • 50
  • 6
  • Sorry, but it is unclear what you ask. We do not know your database scheme, not the final query that is executed. – arkascha Nov 15 '16 at 07:08
  • As an aside, it is **dangerous** to concatenate strings together to make a query. Always use a prepared statement for this. I have a feeling that your problem will disappear when you fix that... – Toby Speight Nov 17 '16 at 17:10
  • If you don't know about SQL injection, you should read ["How can I prevent SQL injection in PHP?"](/q/60174). – Toby Speight Nov 17 '16 at 18:35
  • @TobySpeight Aside: The OP originally featured `->` on each line; those were correct but have since been edited out. – Luke Briggs Nov 17 '16 at 19:07
  • @Luke - looks like the first edit; I've reinstated those. – Toby Speight Nov 17 '16 at 21:57

2 Answers2

1

You've got to put it in quotes:

->where('a.email = "' . $user->email.'"')

Note that you'll need to escape that email address. This depends on the SQL library that you're using. One old example is like this:

->where('a.email = "' . mysql_real_escape_string( $user->email ).'"')

Why 'old'?

You should really be using prepared statements instead of escaping. I would assume that the library you're using supports them, but I can't say for certain there. If it doesn't, you should consider dropping it and follow the solution in the previous link.

What's actually happening?

Your query comes out like this:

where a.email=hello@site.com and a.dtstart>342734..

SQL is treating the email as part of the query. This is the basis of what's called an SQL injection attack. For example, imagine if the email was set to this:

"" or (drop table users)

Making your query look like this:

where a.email="" or (drop table users) and a.dtstart>342734..

..Which could cause some major problems! Don't trust anything that comes from the user.

Community
  • 1
  • 1
Luke Briggs
  • 3,745
  • 1
  • 14
  • 26
  • thanks for your explanation! This solution didn't work for me though. The error dissapeared but I didn't retrieve any rows. – Olga Nov 15 '16 at 10:10
  • The accepted answer is the same as this one, unless you grabbed the escaped line (as mentioned, that depends on the SQL library you're using, and if it's already escaped). The accepted answer is probably vulnerable to SQL injection so be extremely careful :) – Luke Briggs Nov 15 '16 at 16:36
  • 1
    I've read that [prepared statements are better](/q/4771984) than trusting one program to escape strings for another - perhaps you'd like to demonstrate how to do that? I would, but I don't know PHP; it does seem that ["How can I prevent SQL injection in PHP?"](/q/60174) is relevant here. – Toby Speight Nov 17 '16 at 18:30
  • @TobySpeight You're correct there - prepared statements are far better, but it depends on which SQL library is being used and if it supports creating them. The one in use here isn't one I immediately recognize, so I'm unsure if it does (thus I went with the route of suggesting escaping instead). – Luke Briggs Nov 17 '16 at 19:07
  • @TobySpeight Added more context about prepared statements; p.s. thanks for the formatting! – Luke Briggs Nov 17 '16 at 19:19
-1

$user->email is string so you need to use ' single brackets with email.

Try below 

$query
    ->select(array('a.dtstart','a.cal_id','b.name','a.email'))
    ->from('#__pbbooking_events AS a')
    ->join('INNER', '#__pbbooking_cals AS b ON (a.cal_id = b.id)')
    ->where("a.email = '" . $user->email. "'") // $user->email is string
    ->where(' a.dtstart > '. $query->currentTimestamp())
    ->order('a.dtstart ASC');
Gopal Joshi
  • 2,350
  • 22
  • 49