Page 1 of 1

php5.2.5 and mssql_connect... php too fast???

Posted: Mon Jan 28, 2008 11:59 am
by redherring
Hi there -

I have a script that interacts with an sql database. Since upgrading from php 4.3.8 to 5.2.5 I've found that most of the time the script no longers returns data... it doesn't produce an error or anything, it just doesn't return any data. Then, "once in a while" - that's an official unit of measurement - it will return the correct data.

It's almost as if, as per someone's post here - http://bugs.php.net/bug.php?id=29074 - "php is getting too fast and giving up before a response is received from SQL 2000?"

Appreciate your thoughts on this.

Thanks.

Re: php5.2.5 and mssql_connect... php too fast???

Posted: Mon Jan 28, 2008 12:20 pm
by Eran
There's no such thing as PHP giving up before getting a response from a database. Slow database queries are a major factor in slowing down PHP scripts...
More probable you have a bug in your legacy code that fetches the data, since it was written for PHP 4+
If you'd post the code that fetches the data from the database you might get help locating that bug

Re: php5.2.5 and mssql_connect... php too fast???

Posted: Mon Jan 28, 2008 12:28 pm
by redherring
Yea, sure seems like a weird concept to me, although I bought into it as I could "swear" that sometimes i actually do get data returned. Regardless, here's the corresponding code:

*************************************

Code: Select all

 
 
$clientID = $_GET['clientID'];
 
$server = "localhost,1433";
$username = 'myusername';
$password = 'mypassword';
 
$sqlconnect = mssql_connect($server, $username, $password);
$sqldb = mssql_select_db("mydatabase",$sqlconnect);
$sqlquery = "SELECT companyname AS 'clientname' FROM mytable WHERE companynum = $clientID;";
$result = mssql_query($sqlquery);
 
while ($row=mssql_fetch_array($result)){
$companyname = $row['clientname'];}
 
mssql_close($sqlconnect);
 
 
*****************************

Many thanks.

Re: php5.2.5 and mssql_connect... php too fast???

Posted: Mon Jan 28, 2008 12:58 pm
by JAM
You should add a check to see if you get hits aswell, as example;

Code: Select all

if (mssql_num_rows($result) == 1) {
    // do stuff
} else {
    // debug code here as result was unexpected
}
And perhaps the use of mssql_result() instead of the fishy looking while-loop?

Re: php5.2.5 and mssql_connect... php too fast???

Posted: Mon Jan 28, 2008 1:03 pm
by Mordred
What happens if
a) you change db host to 'localhost:1433' (: instead of , - at least that's how it works for mysql, I have no experience with mssql)
b) read about SQL injection
c) check error conditions
d) crerate an index on companynum if there's lots of rows
e) use LIMIT 1 and call fetch_array only once

Re: php5.2.5 and mssql_connect... php too fast???

Posted: Mon Jan 28, 2008 1:31 pm
by redherring
Thanks.

The query actually returns no results, although clearly it should... one per value of clientID (as such, the note about the fishy looking while loop is even more relevant).

It does not like "localhost:1433" (with a colon vs. a comma).

MSSQL sure seems to be enabled properly via a php-info.

Re: php5.2.5 and mssql_connect... php too fast???

Posted: Mon Jan 28, 2008 4:15 pm
by Mordred
redherring wrote:(as such, the note about the fishy looking while loop is even more relevant)
I now realize that in mssql it's SELECT TOP 1 blabla, not SELECT blabla LIMIT 1 as in MySQL.
What happens if you try this from ... errr ... does mssql come with a command-line interface? Try it not through your app, but from some administrative interface of sorts.
Don't forget the SQL injection as well.

Re: php5.2.5 and mssql_connect... php too fast???

Posted: Mon Jan 28, 2008 6:46 pm
by Eran
As others mentioned, you should filter your variables and quote them before you insert them into the sql string.
for example -
... companynum = `" . htmlentities($clientID) . "`";
If you know that $clientID can only be an integer it is even prefered to cast it directly - ... companynum = `" . (int) $clientID . "`";

You should dump the query result (var_dump) to see if it returns a true or false. false would indicate an error in the query itself while true indicates no rows were returned.

Also check the level of error reporting you are using, it should be set to E_ALL (you can set it manually using error_reporting(E_ALL)) to make sure you aren't missing any possible errors

Re: php5.2.5 and mssql_connect... php too fast???

Posted: Tue Jan 29, 2008 12:43 am
by Mordred
Oh, my require('deity.inc.php')!
pytrin wrote:As others mentioned, you should filter your variables and quote them before you insert them into the sql string.
for example -
... companynum = `" . htmlentities($clientID) . "`";
If you know that $clientID can only be an integer it is even prefered to cast it directly - ... companynum = `" . (int) $clientID . "`";

You should dump the query result (var_dump) to see if it returns a true or false. false would indicate an error in the query itself while true indicates no rows were returned.

Also check the level of error reporting you are using, it should be set to E_ALL (you can set it manually using error_reporting(E_ALL)) to make sure you aren't missing any possible errors
No, it's ... wrong. Plainly and (almost) entirely.
Check your database docs for how to escape, IIRC mssql didn't have an explicit escape function like MySQL (... which has two, and both of them not quite right, but still ... ), but maybe addslashes() should work okay if you're using utf-8 or latin1 for talking to your database.

var_dump() and error_reporting() are debug things, they shouldn't be run in production environment. There's "if ()" instead of the first, and server-side settings for the latter. If you don't have such control over your server, make sure your scripts include a common file, and put error_reporting(E_NONE) in it. Only your development server at home should have error_reporting.
As others mentioned, you should filter your variables and quote them before you insert them into the sql string.
Now this is correct, and very important too.

Re: php5.2.5 and mssql_connect... php too fast???

Posted: Tue Jan 29, 2008 2:33 am
by Chris Corbyn
Mordred wrote:Check your database docs for how to escape, IIRC mssql didn't have an explicit escape function like MySQL (... which has two, and both of them not quite right, but still ... ), but maybe addslashes() should work okay if you're using utf-8 or latin1 for talking to your database.
PDO FTW! (K, what have done to us??) :)

http://au2.php.net/pdo