Difference between local SQL and host SQL

[nathj]

Hi,

I have a problem with some SQL inside a php file. The tricky thing is that the code works just fine locally and I only have the problems when I load it to the host.

The code is used to check if the desired username exists in the database. If it does then the user is informed, at the time of entering the data, using an AJAX process. Part of this process call a php file (datacheck.php) with a query string that contains the item to check. The code that is the problem is this:

$lcItemToCheck = $_GET['tocheck']
$lcCheckSQL = 
"SELECT COUNT(*) AS test 
FROM credential 
WHERE userName = $lcItemToCheck" ;
/* I have tried adding '' around the variable, I have tried ending the SQL at =" and adding the variable to it, both with and without ''. 
The query ... userName = '" . $lcItemToCheck ."'"; seems to check the database for 'nathj07' which isn't there, I need to check for nathj07.
 
$result = mysql_query($lcCheckSQL);
 

When the SQL is executed it returns the following error:

 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 '\'nathj07\'' at line 3
 

nathj07 is what was entered in the form. This item is the database, I added it for testing purposes and when I run this locally it works a treat.

Does anyone have any suggestions on what is causing the problem or how to fix it?

Many thanks
nathj

[mwasif]

[PHP]$lcItemToCheck = mysql_real_escape_string($_GET['tocheck']);
$lcCheckSQL =
"SELECT COUNT(*) AS test
FROM credential
WHERE userName = '$lcItemToCheck'" ;

$result = mysql_query($lcCheckSQL) or die(mysql_error());[/PHP]

You must have to use single quotes around $lcItemToCheck, because you are comparing a string column. Look at mysql_real_escape_string(), you'll know why I used this.

[nathj]

[PHP]$lcItemToCheck = mysql_real_escape_string($_GET['tocheck']);
$lcCheckSQL =
"SELECT COUNT(*) AS test
FROM credential
WHERE userName = '$lcItemToCheck'" ;

$result = mysql_query($lcCheckSQL) or die(mysql_error());[/PHP]

You must have to use single quotes around $lcItemToCheck, because you are comparing a string column. Look at mysql_real_escape_string(), you'll know why I used this.

Thanks for that, it is most helpful.

However, the username is stored as nathj07 and I am now looking for 'nathj07' and so this comes back and says the item is not found when really it should be found.

Why would it work on my laptop under localhost but not on the server? (that's more a curiosity than a real question). Is there a way of getting this to return the correct result?

Many thanks
nathj

[mwasif]

1. Make sure you are connecting to the correct database
2. Make sure you have required data in the table
3. Make sure you are using the exact column name (take care of case)
4. echo the resultant query and execute that query online (in phpMyAdmin or any GUI) to verify the results.
5. Show the code where you verifying the existence of the record

[nathj]

1. Make sure you are connecting to the correct database
2. Make sure you have required data in the table
3. Make sure you are using the exact column name (take care of case)
4. echo the resultant query and execute that query online (in phpMyAdmin or any GUI) to verify the results.
5. Show the code where you verifying the existence of the record

Hi mwasif

1. Yep connecting to the right database and table
2. the data is in the table
3. Column name is correct allowing for case sensitivity
4. Here's the query:

         SELECT COUNT(*) AS test                  FROM credential                  WHERE userName = '\'nathj07\''
 

5. The code to verify the existence of the record is:

if($result)
        {                
        while ($row = mysql_fetch_array($result))
        {
            $lnCount = $row['test'];
            if ($lnCount >= 1)
             {
                 $lcToDisplay = $lcErrorText;
             }
        }
        }
        else
        {
        $lcToDisplay = mysql_error();
        }
 
 

The variables are set under a control structure earlier in the procedure. Basically at the start all required data is labelled in red, when it is valid it is labelled in black. If, as in user name, it is already in the DB a specific warning in re is printed.


Thanks for all the help.
nathj

[mwasif]

SELECT COUNT(*) AS test
FROM credential
WHERE userName = '\'nathj07\''

Did you notice backslash and single quote (\') around nathj07? Why is it? Are you inserting single quotes in the form field? Are these single quotes are the part of username? If these are not the part of usename then debug the code to determine the cause of this.

[nathj]

SELECT COUNT(*) AS test
FROM credential
WHERE userName = '\'nathj07\''

Did you notice backslash and single quote (\') around nathj07? Why is it? Are you inserting single quotes in the form field? Are these single quotes are the part of username? If these are not the part of usename then debug the code to determine the cause of this.

I was a bit curious about that I must admit. I have done a little bit of debugging (echo the item from the query string) and can see that it contains " ' " makrs around the string. I am not explcitally adding these when the user fills in the form.

What could cause their appearance and what can be done to correct this? I tried a strtreplace but withouit much luck, any ideas?

Thank you for al your help, I really appreciate it.
nathj

[mwasif]

Check your HTML form fields.

[nathj]

Check your HTML form fields.

Ok, here's the form field in question:

<input id="username" type="text" size="47" onchange="validateItem('usernamelabel',this.value,'Username:',5,false,true,3);" alt="Username, for use on the Forum" title="Username, for use on the Forum" />
 

What could be the problem with this? I don't see anything that would add '' cahracters around the user entered value.

Thanks for all your help.
nathj

[mwasif]

<input id="username" type="text" size="47" onchange="validateItem('usernamelabel',this.value, 'Username: ',5,false,true,3);" alt="Username, for use on the Forum" title="Username, for use on the Forum" />

what is the function of validateItem()?
Where is the 'name' in the <input>?

[nathj]

what is the function of validateItem()?
Where is the 'name' in the <input>?

validateItem() is a java script function that, in this case, checks that the user name is between 8 and 16 characters and then checks the database to ensure it is not already in use. What do you mean by 'name'? I am testing the username on the form. It is during the JS function that the php I am having difficulty with is called. This is donoe using the HTTPRequest object that onstatechange calls the php that checks the database.

If I could ensure that the comparison either has " ' " present on both sides or missing on both sides that would be great.
(preferrably the latter)

Cheers
nathj

[ak1dnar]

validateItem() is a java script function that, in this case, checks that the user name is between 8 and 16 characters and then checks the database to ensure it is not already in use. What do you mean by 'name'? I am testing the username on the form. It is during the JS function that the php I am having difficulty with is called. This is donoe using the HTTPRequest object that onstatechange calls the php that checks the database.

If I could ensure that the comparison either has " ' " present on both sides or missing on both sides that would be great.
(preferrably the latter)

Cheers
nathj

Add name="username" to the html form element with id="username" as name attribute is the traditional HTML form elements identification.

Now before you post the data to the server side via HTTPRequest, print back the form element for double checking the post url.

try ,

 posturl = "username="+variable; 
alert(posturl)
 

then on server side get the user entered values to the script variables with

 $USERNAME = $_REQUEST['usernname'];

Then Pass it to Query String

[PHP]$lcCheckSQL =
"SELECT COUNT(*) AS test
FROM credential
WHERE userName ='$USERNAME' ";[/PHP]

[nathj]

=

Add name="username" to the html form element with id="username" as name attribute is the traditional HTML form elements identification.

Now before you post the data to the server side via HTTPRequest, print back the form element for double checking the post url.

try ,

 posturl = "username="+variable; 
alert(posturl)
 

then on server side get the user entered values to the script variables with

 $USERNAME = $_REQUEST['usernname'];

Then Pass it to Query String

[PHP]$lcCheckSQL =
"SELECT COUNT(*) AS test
FROM credential
WHERE userName ='$USERNAME' ";[/PHP]

Thanks for the help. However, (life is never simple is it?) I havetried the suggestion and it makes no difference. It seems to me that the entry in the database is without ' as you would expect but the variable passed across is enclosed in '. So when the code checks the database is says nathj07 does not equal 'nathj07' and so the desired username is valid.

The code at present (server side - as clled by onstatechange):

if(isset($_GET['check']) && isset($_GET['tocheck']))
    {
 
        //establish connection - using dataobject.php did not work - for some reason the file was not available
        $lvCon = mysql_connect('host', 'user', 'password');
        //$lvCon = mysql_connect('host', 'user', 'password); 
        if (!$lvCon)
        {
            die('Could not connect: ' . mysql_error());
        }
        mysql_select_db(database, $lvCon);
        //mysql_select_db(database, $lvCon);
 
        $lnCheckType    = mysql_real_escape_string($_GET['check']);
        $lcItemToCheck    = mysql_real_escape_string($_GET['tocheck']);    
 
        // branch the code
        switch($lnCheckType)
        {
            case 5: // check for presence of username, will only ever be one match or no match
                $lcToDisplay = "Username:";
                $lcErrorText = "<span class='warninglabel'>Username in use:</span>";
 
                $lcCheckSQL = 
                "SELECT COUNT(*) AS test 
                FROM credential 
                WHERE userName = '$lcItemToCheck'" ; 
                break;    
 
            case 2:// check for the presence of an email, this should be unique in the database
                $lcToDisplay = "E-Mail:"; 
                $lcErrorText = "<span class='warninglabel'>E-Mail already regisitered:</span>";
                $lcCheckSQL =
                "SELECT COUNT(*) AS test
                FROM eaddress
                WHERE eAddressType = 1 AND
                eAddress = '$lcItemToCheck'";
                break;
        }    
 
        $result = mysql_query($lcCheckSQL);
        if($result)
        {                
        while ($row = mysql_fetch_array($result))
        {
            $lnCount = $row['test'];
            if ($lnCount >= 1)
             {
                 $lcToDisplay = $lcErrorText;
             }
        }
        }
        else
        {
        $lcToDisplay = mysql_error();
        }
 
        echo $lcToDisplay;
    }        
 

You will see from this that I run a similar check on the email address as well. The theory is that once one works they will both work.

Originally I didi not have the 'mysql_real_escape_string' being used and the code worked a treat on localhost. However, I have loaded this to the test server and it doesn't work without this line, reporting an error in the SQL. However, with the lines in it simply doesn't find a match. It is most peculiar.

Any further suggestionons on this issue?

Many thanks
nathj

[ak1dnar]

Pass the URL parameters to ServerSideScript.php by executing its absolute URL of the script.

http://localhost/siteroot/ServerSideScript.php?check=somevalue&tocheck=someothervalue

And Let me know whether its working or Not.

[nathj]

Pass the URL parameters to ServerSideScript.php by executing its absolute URL of the script.

http://localhost/siteroot/ServerSideScript.php?check=somevalue&tocheck=someothervalue

And Let me know whether its working or Not.

Hi Ajaxrand,

Upon trying the absolute path approach I spotted that I had enclosed the query string parameter in ' so when extracting it it was setting the variable to 'nathj07'. I removed this and it works perfectly. I didn't need the absolute path.

However, had you not sugested it I don't think I would have closely examined that line of code at all. So many many thanks to you for that. It turns out it wasn't a SQL or PHP problem but a javascript problem.

I guess the important thing is that it works and that I have learnt something from this.

Thanks again for your help, I couldn't have done without you - that goes for everyone who helped.

Well, best go see who I can help now.
nathj

[ak1dnar]

Hi Ajaxrand,

Upon trying the absolute path approach I spotted that I had enclosed the query string parameter in ' so when extracting it it was setting the variable to 'nathj07'. I removed this and it works perfectly. I didn't need the absolute path.

However, had you not sugested it I don't think I would have closely examined that line of code at all. So many many thanks to you for that. It turns out it wasn't a SQL or PHP problem but a javascript problem.

I guess the important thing is that it works and that I have learnt something from this.

Thanks again for your help, I couldn't have done without you - that goes for everyone who helped.

Well, best go see who I can help now.
nathj

Well done! best of luck with your project....
-Ajaxrand