PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Mon Sep 25, 2017 1:52 pm

All times are UTC - 5 hours




Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 29 posts ]  Go to page 1, 2  Next
Author Message
PostPosted: Thu Jan 22, 2004 5:09 pm 
Offline
DevNet Resident
User avatar

Joined: Thu Oct 16, 2003 5:49 pm
Posts: 1972
Location: Manchester (UK)
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.
Syntax: [ Download ] [ Hide ]
 
<?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.
Syntax: [ Download ] [ Hide ]
 
$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.
Syntax: [ Download ] [ Hide ]
 
$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
 


Top
 Profile  
 
 Post subject: hack possible
PostPosted: Thu Jan 22, 2004 8:33 pm 
Offline
Forum Contributor

Joined: Wed Jan 14, 2004 11:21 am
Posts: 313
Location: planet earth
... 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 "'"

Quote:
$sql = "SELECT name, email FROM user WHERE id='".$id."';
e.g.:
$sql = "SELECT name, email FROM user WHERE id='5';


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jan 22, 2004 8:36 pm 
Offline
DevNet Resident
User avatar

Joined: Thu Oct 16, 2003 5:49 pm
Posts: 1972
Location: Manchester (UK)
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.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jan 23, 2004 5:57 am 
Offline
Forum Contributor

Joined: Wed Jan 14, 2004 11:21 am
Posts: 313
Location: planet earth
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.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jan 23, 2004 6:11 am 
Offline
Her Royal Site Adminness
User avatar

Joined: Tue Apr 23, 2002 2:21 am
Posts: 5371
Location: Essex, UK
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


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jan 23, 2004 8:22 am 
Offline
Forum Newbie
User avatar

Joined: Tue Jul 08, 2003 6:13 am
Posts: 16
Location: UK
These days I always use this bit of code for generating ids rather than relying on numbers;

Syntax: [ Download ] [ Hide ]
<?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'.


Top
 Profile  
 
 Post subject: i love forums
PostPosted: Fri Jan 23, 2004 8:41 am 
Offline
Forum Contributor

Joined: Wed Jan 14, 2004 11:21 am
Posts: 313
Location: planet earth
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?)


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jan 23, 2004 8:42 am 
Offline
Her Royal Site Adminness
User avatar

Joined: Tue Apr 23, 2002 2:21 am
Posts: 5371
Location: Essex, UK
I tend to prefer an auto-incremented number 'cause it makes it easier to store it as a foreign key in another table.

Mac


Top
 Profile  
 
 Post subject:
PostPosted: Sat Jan 24, 2004 2:16 am 
Offline
DevNet Master

Joined: Thu Jan 30, 2003 9:26 pm
Posts: 2893
Location: Glasgow, Scotland
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.


Top
 Profile  
 
 Post subject:
PostPosted: Sat Jan 24, 2004 2:42 am 
Offline
DevNet Resident
User avatar

Joined: Thu Oct 16, 2003 5:49 pm
Posts: 1972
Location: Manchester (UK)
Quote:
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.

Quote:
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 ;)


Top
 Profile  
 
 Post subject:
PostPosted: Mon Feb 09, 2004 2:49 pm 
Offline
Forum Contributor
User avatar

Joined: Tue May 20, 2003 4:49 pm
Posts: 400
this works as well as seperating the variable from the query function:
Syntax: [ Download ] [ Hide ]
<?php
mysql_query($query = "SELECT * FROM table;");
print"$query";
?>


Top
 Profile  
 
 Post subject:
PostPosted: Sun Feb 15, 2004 3:09 am 
Offline
Forum Commoner
User avatar

Joined: Wed Feb 04, 2004 1:26 am
Posts: 87
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:

Syntax: [ Download ] [ Hide ]
$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:
Syntax: [ Download ] [ Hide ]
$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.


Top
 Profile  
 
 Post subject:
PostPosted: Sun Feb 15, 2004 1:19 pm 
Offline
DevNet Master

Joined: Thu Jan 30, 2003 9:26 pm
Posts: 2893
Location: Glasgow, Scotland
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.


Top
 Profile  
 
 Post subject:
PostPosted: Sun Feb 15, 2004 1:35 pm 
Offline
Site Admin

Joined: Thu Apr 18, 2002 3:14 pm
Posts: 1767
Location: Montreal, CA
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.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Mar 09, 2004 11:28 am 
Offline
DevNet Resident
User avatar

Joined: Wed Sep 25, 2002 7:47 pm
Posts: 1708
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 ;)


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 29 posts ]  Go to page 1, 2  Next

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 9 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group