[RESOLVED] Email Searches - the safe way?

[PHPsites]

Hello.

I am looking for some help regarding searching an email field in MYSQL. Currently have a form that allows customers to search for other customers based on email.

I do not want someone to type in "mail" as their search string and return every customer with "gmail", "hotmail", "webmail", etc. in their email address.

Any idea of how to do this? The ideal situation would be a MYSQL query that looks only at the string preceding the @ sign in the email address.

So, a search for "mail" would return "[e-mail]" but not "[e-mail]".

Any ideas? Thanks in advance.

[bpat1434]

SELECT * FROM `table` WHERE `email` LIKE '{$searchStr}@%'

[PHPsites]

I will try this out. However, will this work with the following example?

Email: [e-mail]

What if $searchStr in your example is "test"? Will that return this member?
Or will this member only be returned if "mail" was searched for?

Like I said, I will try it out but if anyone had an answer, that would also be great. Thanks.

[bretticus]

PHPsites;10881739 wrote:

I will try this out. However, will this work with the following example?

Email: [e-mail]

What if $searchStr in your example is "test"? Will that return this member?
Or will this member only be returned if "mail" was searched for?

Like I said, I will try it out but if anyone had an answer, that would also be great. Thanks.

Brett's (other brett) query is for searching for the search term plus @ followed by anything. So in that example only searching for "testemail" will match "[e-mail]." Of course you could put more % characters to match zero or more such as:

SELECT * FROM `table` WHERE `email` LIKE '%{$searchStr}%@%'

Supposedly my example will match the search string anywhere in the username portion of the email address. I never do this, so try it out. If that doesn't work read up on REGEXP at the MySQL manual.

[PHPsites]

bretticus;10881740 wrote:

Supposedly my example will match the search string anywhere in the username portion of the email address. I never do this, so try it out. If that doesn't work read up on REGEXP at the MySQL manual.

Thanks Brett. However, is there a reason why you never do this? Obviously I am still learning but I am trying to learn the RIGHT way at all times.

Does this subject my site to hacking possibilities? Is there a better way to construct this and/or avoid this?

I appreciate all the help and will definitely read up on REGEXP just curious as to the safe way to proceed.

Thanks.

[bpat1434]

I think by "never" he meant that he just hasn't done it, not that it wasn't necessarily a good thing to do.

[bretticus]

bpat1434;10881882 wrote:

I think by "never" he meant that he just hasn't done it, not that it wasn't necessarily a good thing to do.

Ohhh, yep, bpat is absolutely right. I just haven't done this before (match emails in this manner), that's all. I just mentioned that because, off the top of my head, I believe that % is a moniker meaning zero or more. I just wasn't sure if it still meant "zero or more" when not used on the ends of the expression (beginning and end.)

Does this subject my site to hacking possibilities? Is there a better way to construct this and/or avoid this?

This is fine. Just use mysql_real_escape_string() to escape your post/get/cookie variables.

[PHPsites]

Thanks a lot for the response. I will play with this.

bretticus;10881903 wrote:

This is fine. Just use mysql_real_escape_string() to escape your post/get/cookie variables.

Probably off the subject and maybe needs to be addressed in a separate post but....
Instead of using mysql_real_escape_string() I have been using PDO and prepared statements. I believe PDO is a better way to go. Opinion?

If I don't get some responses to this one shortly, I will mark resolved and move on. Thanks a lot everyone.

[bretticus]

PDO is an extension that implements an interface that allows you to use an abstraction above the database driver. It's becoming very popular and I believe that the setting of parameters as inputs for query values in prepared SQL statements takes care of escaping for you automatically. I say use it! If by some chance you get stuck with php4 then you can write your own class objects that provide an abstraction layer for database connectivity. For example, I wrote my database class years before php 5 was available. I still use it (although I should probably start writing all my new code using PDO.) 🙂