terry_ally
asked on
PHP MYSQL Select statement
I need assistance to correct a flaw in my PHP coding.
I have an online mailing list and I have written an interface for subscribers to change their information. At the moment they could retrieve it using only their subscriber_id number but there is a flaw in this. Anyone could enter a random number, retrieve and change someone else's information EXCEPT the email address. I made the email field a readonly field. However, the majority of requests that I am receiving are for email address changes. So I want to change the retrieve process, I want subscribers to be able to retrieve their records using BOTH subscriber_id AND email address and this is where I am running into problems.
Attached is the code I currently use. I've changed Line 6 to:
and the SELECT statement on Line 10 to:
I am getting an error message which states:
I need assistance in understanding (1) How to retrieve the record using both the subscriber_id and the email fields and (2) how to stop the error message being reported to the screen.
Thanks
Terry
I have an online mailing list and I have written an interface for subscribers to change their information. At the moment they could retrieve it using only their subscriber_id number but there is a flaw in this. Anyone could enter a random number, retrieve and change someone else's information EXCEPT the email address. I made the email field a readonly field. However, the majority of requests that I am receiving are for email address changes. So I want to change the retrieve process, I want subscribers to be able to retrieve their records using BOTH subscriber_id AND email address and this is where I am running into problems.
Attached is the code I currently use. I've changed Line 6 to:
if (is_numeric ($_GET['subscriber_id']) && is_string($_GET['email']) ) {
and the SELECT statement on Line 10 to:
$query = "SELECT * FROM dada_subscribers WHERE subscriber_id={$_GET['subscriber_id']} AND email={$_GET['email']}";
I am getting an error message which states:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@hotmail.com' at line 1
The query was SELECT * FROM dada_subscribers WHERE subscriber_id=555 AND email=johndoe@hotmail.com.
I need assistance in understanding (1) How to retrieve the record using both the subscriber_id and the email fields and (2) how to stop the error message being reported to the screen.
Thanks
Terry
not sure with PHP - do you use \" to escape the " character?
I like to end my sql with a ; too.
$query = "SELECT * FROM dada_subscribers WHERE subscriber_id={$_GET['subscriber_id']} AND email=\"{$_GET['email']}\";";
I like to end my sql with a ; too.
This might be simpler to understand and read and solves the apostrophe problem.
$query = "SELECT * FROM dada_subscribers WHERE subscriber_id='" . $_GET['subscriber_id'] . "' AND email='" . $_GET['email'] . "'";
First, that's a very good step you are taking, by recognizing that vulnerability that anyone could put any ID in.
However, that is VERY insecure code. Never, EVER use the value of a $_GET or $_POST or any user-provided variable in a query without cleaning it first. If you make that code live, you could give hackers a way to permanently delete all of the records in that database (not kidding). All they have to do is create an "email address" that has SQL code in it and you're screwed.
This:
$query = "SELECT * FROM dada_subscribers WHERE subscriber_id={$_GET['subs criber_id' ]} AND email={$_GET['email']}";
Should look like:
$query = "SELECT * FROM dada_subscribers WHERE subscriber_id=" . intval($_GET['subscriber_i d']) . " AND email='" . mysql_real_escape_string($ _GET['emai l']) . "'";
What if the person used an easily-accessible tool like Firebug to change their email address in the form (nothing is ever read-only in a web page unless you restrict it on the database permission level)?
Imagine that they change it from:
joe.smith@gmail.com
to:
'; DELETE FROM dada_subscribers; SELECT '
Your query might end up looking like this:
SELECT * FROM dada_subscribers WHERE subscriber_id=123 AND email=''; DELETE FROM dada_subscribers; SELECT '';
Suddenly, anyone has access to run their own database queries on your database and that above one would delete your records.
It's extremely important to be careful with your database queries. Anytime a database query has a variable in it that is coming from a user, make SURE that you're escaping it!
However, that is VERY insecure code. Never, EVER use the value of a $_GET or $_POST or any user-provided variable in a query without cleaning it first. If you make that code live, you could give hackers a way to permanently delete all of the records in that database (not kidding). All they have to do is create an "email address" that has SQL code in it and you're screwed.
This:
$query = "SELECT * FROM dada_subscribers WHERE subscriber_id={$_GET['subs
Should look like:
$query = "SELECT * FROM dada_subscribers WHERE subscriber_id=" . intval($_GET['subscriber_i
What if the person used an easily-accessible tool like Firebug to change their email address in the form (nothing is ever read-only in a web page unless you restrict it on the database permission level)?
Imagine that they change it from:
joe.smith@gmail.com
to:
'; DELETE FROM dada_subscribers; SELECT '
Your query might end up looking like this:
SELECT * FROM dada_subscribers WHERE subscriber_id=123 AND email=''; DELETE FROM dada_subscribers; SELECT '';
Suddenly, anyone has access to run their own database queries on your database and that above one would delete your records.
It's extremely important to be careful with your database queries. Anytime a database query has a variable in it that is coming from a user, make SURE that you're escaping it!
ASKER
Hi gr8gonzo,
I've carried out your test and attempted to hack the database without success. Thanks for that tip.
I've implemented your solution but a problem has arisen. Once I use the combined subscriber_id and the email in the URL I am able to retrieve the record.
However, if I omit the email address or use an incorrect email address it is not throwing me the error message any longer ("No subscriber record was located").
How do I correct that?
Terry
I've carried out your test and attempted to hack the database without success. Thanks for that tip.
I've implemented your solution but a problem has arisen. Once I use the combined subscriber_id and the email in the URL I am able to retrieve the record.
However, if I omit the email address or use an incorrect email address it is not throwing me the error message any longer ("No subscriber record was located").
How do I correct that?
Terry
Terry,
When you report that an SQL query doesn't work, please share the query string with us.
One way to see the string is
echo $query . "<br />"
Of course, you want to remove the echo statement from production software.
Thanks,
Hugh
When you report that an SQL query doesn't work, please share the query string with us.
One way to see the string is
echo $query . "<br />"
Of course, you want to remove the echo statement from production software.
Thanks,
Hugh
ASKER
Hi Hugh,
There was no error message and there should have been. I said that in my post. It returns a blank data form.
Terry
There was no error message and there should have been. I said that in my post. It returns a blank data form.
Terry
Subscriber is a Number so subscriber = 555 should work
try to change the email format
in regular sql it is a text so it needs single quotes around it
email=johndoe@hotmail.com will not work, you must pass through the quotes to the sql
email='johndoe@hotmail.com '
php should let it slide but perhaps you have a freak version which needs this change
$query = "SELECT * FROM dada_subscribers WHERE subscriber_id={$_GET['subs criber_id' ]} AND email='{$_GET['email']}'";
try to change the email format
in regular sql it is a text so it needs single quotes around it
email=johndoe@hotmail.com will not work, you must pass through the quotes to the sql
email='johndoe@hotmail.com
php should let it slide but perhaps you have a freak version which needs this change
$query = "SELECT * FROM dada_subscribers WHERE subscriber_id={$_GET['subs
Terry,
I understand there's no error message. I get that. Happens to me more often than I want to admit.
Print the query string to see what it says. My guess is that it says something different than what you intend. (At least that's what happens to me more often than I want to admit.)
What do you mean by blank data form? Do you mean it's empty or do you mean the page has no text? If "no text" then I suggest you look in your log file (error_log, probably) to see what went wrong. PHP syntax errors end up in my error_log file. If you can't get to the log file, you could run your script through PHP at the command line. That may or may not work depending on what you have in your script.
Hugh
I understand there's no error message. I get that. Happens to me more often than I want to admit.
Print the query string to see what it says. My guess is that it says something different than what you intend. (At least that's what happens to me more often than I want to admit.)
What do you mean by blank data form? Do you mean it's empty or do you mean the page has no text? If "no text" then I suggest you look in your log file (error_log, probably) to see what went wrong. PHP syntax errors end up in my error_log file. If you can't get to the log file, you could run your script through PHP at the command line. That may or may not work depending on what you have in your script.
Hugh
ASKER
Hi Hugh,
I printed the query string and it is exactly as it was taken from the URL. Nothing untoward.
Basically, it calls for a record with a unique subscriber_id and the email address that's in the record. When these are correct it returns the record with editable fields (a data entry form).
When I try the subscriber_id with an incorrect email -- according to my code -- it should return an error message. However, it is a returning a blank data form.
Have a look at the code which I attached.
Terry
I printed the query string and it is exactly as it was taken from the URL. Nothing untoward.
Basically, it calls for a record with a unique subscriber_id and the email address that's in the record. When these are correct it returns the record with editable fields (a data entry form).
When I try the subscriber_id with an incorrect email -- according to my code -- it should return an error message. However, it is a returning a blank data form.
Have a look at the code which I attached.
Terry
have you tried splitting the page to
if exists then return <form stuff>
else echo "<br>the records dont match"
if exists then return <form stuff>
else echo "<br>the records dont match"
ASKER
Hi esskayb2d,
Yes, the page is structured like that.
Terry
Yes, the page is structured like that.
Terry
ASKER
Hello everyone,
Unfortunately, none of the above solutions are doing the job for me and I am going to close this question.
Unfortunately, none of the above solutions are doing the job for me and I am going to close this question.
how about pasting your code?
ASKER
I have pasted AND attached the code, if you go back to my original post.
you pasted two lines of code, and the error
it will be easier if you attach the file so that the professionals here can look at it closer
it will be easier if you attach the file so that the professionals here can look at it closer
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
so do you want it to be an OR not an AND?
if they provide either subid or email you want it to find the records?
if they provide either subid or email you want it to find the records?
ASKER
Hi gr8gonzo,
Your solution works when using mysql_fetch_array rather than mysql_fetch_assoc.
Thanks
Terry
Your solution works when using mysql_fetch_array rather than mysql_fetch_assoc.
Thanks
Terry
ASKER
This solution works when using mysql_fetch_array rather than mysql_fetch_assoc.
Open in new window