MySQL Bugs: #3892: function "update" fails to save strings containing the "@" signs in tables

Bug #3892	function "update" fails to save strings containing the "@" signs in tables
Submitted:	26 May 2004 1:50	Modified:	28 Jun 2004 12:18
Reporter:	Timor Hatamy	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.17-nt	OS:	Windows (Windows 2000 avanced server)
Assigned to:		CPU Architecture:	Any

Description:
//A PHP page receives the following data:

$email=$_REQUEST["email"];

//(other variables already exist)

$query="UPDATE sometable
 SET
name=$name,
company=$company,
email=$email
where  
index=$id";

How to repeat:
Go to http://www.php.net and look at the saved email addresses of the bug submissions.
There are about 100000 of them.

PHP site have found a good solution. By replacing the @ signs with "at"
So the email addresses look like this:
xxxATxxx.xx

Unfortunately, I will have to do what they are doing.

Suggested fix:
I work with mysql daily since 4 years ago.

As long as I know, this problem started when you have added the cache function to accelerate the server but please don't take me by words.
From the mysql_error() outputs I can estimate that a string such as
 xxx@xxx.xx is converted into xxx'@'xxx.xx.

I wasn't able to repeat the behavior reported using the
prompt client mysql.exe. Can you provide for us a small test
case ? Thanks.

C:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.18-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table myemail (id int, email char(25));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into myemail values (1,"mikale@thetest.com");
Query OK, 1 row affected (0.00 sec)

mysql> update myemail set email="mikale2@thetest2.com" where id = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from myemail;
+------+----------------------+
| id   | email                |
+------+----------------------+
|    1 | mikale2@thetest2.com |
+------+----------------------+
1 row in set (0.00 sec)

I have found the problem!

Sorry, I was misled and therefore gave you wrong information. This is because of the not ok error messaging of the mysql. MySql works ok when there is a connection to a table but there is no warning when there is no database connection. 

Connection strings are inserted usually like this once and forgotten:
include('/db_conn.php');

Containing the following information for example: 

$db = mysql_connect("localhost","",""); 
mysql_select_db("mydb",$db);

now, if you create a file in a subdirectory relative to the root then the connection string should be like this:

include('./db_conn.php');

But instead of getting a message saying no database or table selected, I have got a bunch of sql errors that eventually would convince anyone that either you don’t know SQL at all or Mysql went wacko.

Yes, there is a database connection error checking but not for all cases and not incase that I have stumbled on. Please understand, I'm working in a jungle of thousands of pages and cannot reconstruct exactly in which case but one thing is sure. I have lost one and half days trying to find out why the update function is not ok instead of knowing that there is no database connection!
The programmers are depends on error messages (like me) as god given commands to correct their mistakes and never doubt about them. 

Mysql should check if there is a database connection before emitting any other error message!

Other Problem
Although here is no place to bring another issue but please now that since the mysql_connect() function replaced with mysql_real_connect() many refuse (including me) to upgrade to a newer version. It is simply a big headache.

Best Regards

Tim

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".