php5.2.5 and mssql_connect... php too fast???
Moderator: General Moderators
-
redherring
- Forum Newbie
- Posts: 6
- Joined: Fri Jan 25, 2008 8:52 pm
php5.2.5 and mssql_connect... php too fast???
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.
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???
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
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
-
redherring
- Forum Newbie
- Posts: 6
- Joined: Fri Jan 25, 2008 8:52 pm
Re: php5.2.5 and mssql_connect... php too fast???
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:
*************************************
*****************************
Many thanks.
*************************************
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???
You should add a check to see if you get hits aswell, as example;
And perhaps the use of mssql_result() instead of the fishy looking while-loop?
Code: Select all
if (mssql_num_rows($result) == 1) {
// do stuff
} else {
// debug code here as result was unexpected
}Re: php5.2.5 and mssql_connect... php too fast???
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
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
-
redherring
- Forum Newbie
- Posts: 6
- Joined: Fri Jan 25, 2008 8:52 pm
Re: php5.2.5 and mssql_connect... php too fast???
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.
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???
I now realize that in mssql it's SELECT TOP 1 blabla, not SELECT blabla LIMIT 1 as in MySQL.redherring wrote:(as such, the note about the fishy looking while loop is even more relevant)
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???
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
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???
Oh, my require('deity.inc.php')!
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.
No, it's ... wrong. Plainly and (almost) entirely.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
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.
Now this is correct, and very important too.As others mentioned, you should filter your variables and quote them before you insert them into the sql string.
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
Re: php5.2.5 and mssql_connect... php too fast???
PDO FTW! (K, what have done to us??)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.
http://au2.php.net/pdo