Sometimes works, sometimes syntax error?

[ConCon]

Hi all. I've been having this problem and I was hoping someone could catch something I didn't. So here it goes:

I have developed a registration system using PHP and mySQL, with usernames and passwords being encrypted by salt and md5 (and display names getting no encryption) on PHP's end before being inserted into the database. This system normally works brilliantly, creating all registrations et al that goes with. However, as was pointed out to me this evening by one of my friends, there seems to be an occasional mySQL error where it refuses to enter either the username or password. Which of the two I didn't get a chance to figure out because said friend went offline.

My question is, of course, what could be causing such an issue when I've had an influx of 30+ registrations over the course of a few days (after opening the script to public - naturally I didn't catch this error when I was testing) and what can I do to fix it? I'll post any code you ask for, though in this situation I'm not quite sure what to post as I'm not sure exactly what's broken.

[pritaeas]

I suggest you log all mysql errors to a file, so you can determine the cause. That said, do you trap single quotes in the username/password ?

[ConCon]

I suggest you log all mysql errors to a file, so you can determine the cause. That said, do you trap single quotes in the username/password ?

Sorry about that, I thought I said that it was a syntax error. The actual message they received was
the typical syntax error message, with
"near '[encypted bit here] at line 1".

And if you mean whether I do proper escaping, I don't allow single or double quotes by means of regex, and everything is pre-encrypted before touching the database.

If you didn't mean that, please tell me and I'll try to explain anything else better. Honestly I've never come across something like this before. But there has to be an explanation for it, right? :)

[colweb]

If you could post the part of the code where this syntax error occurs, we all can have a look at it and maybe see why (sometimes) a syntax error is thrown in.

[ConCon]

Sure!
First off though: Obviously this isn't the actual pass_hash that I'm using, but it does contain around the same characters. If that makes a difference? I didn't think so, but there might always be something I don't know.

Pass_hash

$pass_hash = "cOdE*-88";

Here's the username bit:

$username = htmlentities($username);
$usernameh = md5($username, $pass_hash);

And the password bit:

$password = htmlentities($password);
$passwordh = md5($password,$pass_hash);

And the query itself:

mysql_query("INSERT INTO [table name] (username,password,displayname,email) VALUES ('$usernameh','$passwordh','$displayname','$email')") or die(mysql_error());

That's all the relevant bits out of my reg script.

[pritaeas]

The only thing I can think of is that perhaps a backslash messes things up (but there shouldn't be any).

I'd add file-logging so you can at least see what is going on. If there is a mysql error, log the query and the error.

[ConCon]

I don't have root access to the server, and I'm not too familiar with mySQL error logs as I've never actually encountered a situation I needed to access one.

Can I change the directory that mySQL writes its error log to or is that a no-go?

As for variables, etc, I haven't come across one that produces a backslash, much less in conjunction with these oddities.

Perusing my code for the tenth time tonight, I'm still as completely stumped as to what's causing the now-and-then errors.

[colweb]

If you can reproduce the error it might help to add some echo's in your code to see the value of the variables. Like so:

// change this line 
mysql_query("INSERT INTO [table name] (username,password,displayname,email) VALUES ('$usernameh','$passwordh','$displayname','$email')") OR die(mysql_error());
// to
$query = "INSERT INTO [table name] (username,password,displayname,email) VALUES ('$usernameh','$passwordh','$displayname','$email')";
echo "<!--  ".$query." -->";
mysql_query($query) or die(mysql_error());

To see the values you need to look at the source code.

The location of the MySQL error log can be changed but only by the system administrator. It contains all errors from all users. Maybe you can ask your hosting party to look in the log and email relevant data to you.

[colweb]

Your code looks okay, so it's strange to get a syntax error now and then. So I tried this code:

<?php
$pass_hash = "cOdE*-88";

$username = "me";
$password = "this password is a bit silly but easy to remember";

$username = htmlentities($username);
$usernameh = md5($username, $pass_hash);
$password = htmlentities($password);
$passwordh = md5($password,$pass_hash);

$user1 = md5($username);
$user2 = md5(md5($username,$pass_hash));

$query = "INSERT INTO [table name] (username,password,displayname,email) 
          VALUES ('$usernameh','$passwordh','$displayname','$email')";
         
echo "<p>username ".$username."</p>\n";
echo "<p>password ".$password."</p>\n";
echo "<p>usernameh ".$usernameh."</p>\n";
echo "<p>passwordh ".$passwordh."</p>\n";
echo "<p>query ".$query."</p>\n";

echo "<p>user1 ".$user1."</p>\n";
echo "<p>user2 ".$user2."</p>\n";

?>

When you open it in a browser the output is this:

username me

password this password is a bit silly but easy to remember

usernameh «†¡áïpßùyY{r<\$

passwordh LîIaA'ÄŽsho‡¬¼%

query INSERT INTO [table name] (username,password,displayname,email) VALUES ('«†¡áïpßùyY{r<\$','LîIaA'ÄŽsho‡¬¼%','','')

user1 ab86a1e1ef70dff97959067b723c5c24

user2 4cb21b536ef3422120f6060a7fcd1f42

And as you can see here, the passwordh variable contains a ' in it. The query must go wrong this way. To solve it, you might want to use md5(md5(....) to generate a long hex number to insert into the database.