Debugging MySQL code .. a few tips.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Debugging MySQL code .. a few tips.

Post by markl999 »

Just the following 3 'tips' can pretty much resolve all common MySQL problems. Unless you've got at least these 3 things then it's not really worth asking for help (imho), as these are the things you'll probably get told to do first ;)

Error Reporting
Put error_reporting(E_ALL); at the top of your script. This will highlight any warnings/notices that maybe affecting your code.
E.g.

Code: Select all

 
<?php
error_reporting(E_ALL);
 
Use mysql_error()
99% of MySQL problems can be resolved using mysql_error(). If queries are failing, it will tell you why.
E.g.

Code: Select all

 
$db = mysql_connect('localhost', 'user', 'password') or die(mysql_error());
mysql_select_db('thedb') or die(mysql_error());
$sql = "SELECT * FROM foo";
$result = mysql_query($sql) or die(mysql_error());
 
Put queries in variables
Putting the queries into a variable enables you to echo out the var to check the query is what you expect it to be.
E.g.

Code: Select all

 
$sql = "SELECT name, email FROM user WHERE id=$id";
echo $sql; //this will show you if $id is set or not, and what it's value is
 
djot
Forum Contributor
Posts: 313
Joined: Wed Jan 14, 2004 10:21 am
Location: planet earth
Contact:

hack possible

Post by djot »

... and it will show perhaps some more data if $id is not checked properly for being a id really. Second is, to put $id in "'"
$sql = "SELECT name, email FROM user WHERE id='".$id."';
e.g.:
$sql = "SELECT name, email FROM user WHERE id='5';
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

Well, i didn't want to go into the whole 'validating input' thing, but yeah, you should validate ;)
I didn't quote the id as id's are generally INT fields and so don't need to be quoted.
djot
Forum Contributor
Posts: 313
Joined: Wed Jan 14, 2004 10:21 am
Location: planet earth
Contact:

Post by djot »

why are field names or variables named "id" always IDs as numbers? They're not always. And it's better and more secure to quote any data passed to mysql, also IDs.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

It's better and more secure to validate all input before you get anywhere near the SQL statement - ensure that what you expect to be a number is, that there is no SQL injection attempt - and then write your SQL statement accordingly. Then you can quote the strings and leave the INT's unquoted and not be anymore secure or insecure than if you had quoted the lot plus you'll be sending MySQL the data type it expects.

Mac
User avatar
code_monkey
Forum Newbie
Posts: 16
Joined: Tue Jul 08, 2003 6:13 am
Location: UK
Contact:

Post by code_monkey »

These days I always use this bit of code for generating ids rather than relying on numbers;

Code: Select all

<?php
 
$unique_id = md5(uniqid(rand(),1));
 
$token_result = mysql_query("SELECT id FROM table WHERE id='$unique_id'",$db);
 
while($num_rows = mysql_num_rows($token_result)){
                        
    $unique_id = md5(uniqid(rand(),1)); 
                        
}
 
 
?>
Then you just use $unique_id when you insert the data into your database. You get a value of something like 'c6127375320d85153ba1b334ab18330a'.
djot
Forum Contributor
Posts: 313
Joined: Wed Jan 14, 2004 10:21 am
Location: planet earth
Contact:

i love forums

Post by djot »

thats why i like forums that much :)
talking around a topic with no real end and total confusion of the one who opened the topic ...
(new topic for that hypothese?)
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

I tend to prefer an auto-incremented number 'cause it makes it easier to store it as a foreign key in another table.

Mac
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Good first post. Just one thing to add: sticking a __FILE__ and __LINE__ somewhere in the die() output can be useful. If there are many queries in the script you can quickly find the code causing the problem.
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

talking around a topic with no real end and total confusion of the one who opened the topic
I'm of the opinion you're the confused one. You've taken a topic about debugging tips and picked on it's basic examples to turn it into a validation topic. If i'd have put all the user validation in there then it would have taken the focus away from what i was trying to point out, and confused the people the post was aimed at.
thats why i like forums that much .. and total confusion of the one who opened the topic
These sort of replies are why i hate forums so much ;)
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Post by liljester »

this works as well as seperating the variable from the query function:

Code: Select all

<?php
mysql_query($query = "SELECT * FROM table;");
print"$query";
?>
User avatar
Michael 01
Forum Commoner
Posts: 87
Joined: Wed Feb 04, 2004 12:26 am

Post by Michael 01 »

Great thread topic markl999.
Twig and yourself have about the same ideas I implement, but here are a few more that are pretty basic, but overlooked.

You should try to use different variables in your SQL return statements though. One of the downfalls of such queries, is that most people use something like:

Code: Select all

$query=
$return=
$data=
Things along those lines are pretty easy targets for variable sniffers, and those with some common sense. Take a look around these very forums, and count how many people use $sql, $query, etc....Its pretty astronomical.

I tend to use off the wall variable names thanks to a friend of mine who used the dictionary, and than replaced letters withen a common word making it real hard for things as 'Jack the Ripper'. If they are in your head, or on paper, only you are going to know, or at least are going to make life a pain for those trying to hack you.
Things like:

Code: Select all

$smurfpiss=
$chickenbladder=
Error reporting should be turned off at the point of opening public, and on for all development circumstances.

Commenting code for every action doesnt help either. It doesnt take a person with a great deal of knowledge to figure out plain text instructions.

Always vaildate everything using numbers and not text, and go with auto increment. It works, and as long as a map is not drawn to and from your SQL, everything will be cool.

Non Referer address at the top of each script. If it does not equal the link value, than kick em off the page. They got there without clicking on the link, and there are hidden variables you can use to make sure it isnt a hit or miss script. I cant remember the function off hand, but its a slick little function with plenty of power.

No cache, and pragma timeouts. A while back, I had a few scripts thanks to a site that allowed the scripts to cache on my hardrive.

There are more, but the theory "Keep it simple stupid" should be the motto to keep in mind.
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Obscure var names make a script difficult to read and are not strictly necessary.

There is an injection risk if:
(a) you have undefined vars in the global scope and register globals is on
(b) if you don't validate user input ($_COOKIE, $_POST etc)
(c) if you don't quote or escape string input for db queries

If you develop with E_ALL, you'll catch any undefined vars and so register globals settings become irrelevant (at least from a security standpoint - distributed programs should always assume reg globs off for maximum compatibility).

It's recommended not to have anything in the global scope to begin with.
jason
Site Admin
Posts: 1767
Joined: Thu Apr 18, 2002 3:14 pm
Location: Montreal, CA
Contact:

Post by jason »

Concerning variable naming, I have stepped away from $sql, and moved to names like $CheckUsernamePasswordSQL.

Yes, it's longer, and you may think it causes more typing, but I find I spend little time actually coding, and more time with the upkeep of the code. Software is written once, and maintained for life. =) Besides, Zend has variable autocompletion, so I type in the first few letters, and it completes the name for me.

Even more so, I am attempting to remove SQL from my code, and instead, actually really use the Phrasebook pattern to handle the SQL. That way, I avoid the whole SQL thing in my code, and I treat the database practically like a function that gets my data for me.

In this way, I can have a set of functions, and call them all in the same way, and whether it's coming from a database, a SOAP query, or an XML file, it doesn't matter. I retrieve the data in the same way.
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

just thought i'd throw a little bit into this one... another thing to help you along is using PHPMyADMIN..

yes, it does do everything for you, but at the same time it shows you the queries it uses to do all of it's operations. if not anything else, it will help you be more independant for structurizing. i see all the time where people ask how to do a simple query, but fail to see how phpmyadmin does it for a basic idea.

debugging is used this way in that it can verify queries to make sure they work. so, instead of putting it straight into production, try using the query in phpmyadmin and it's nice interface to see where you are going wrong.

btw, excellent writeup mark ;)
Locked