For those unsure of how to use PDO (coming from the mysql_ functions), I made a very, very simple PDO wrapper that is a single file. It exists to show how easy it is to do all the common things applications need done. Works with PostgreSQL, MySQL, and SQLite.

Basically, read it while you read the manual to see how to put the PDO functions to use in real life to make it simple to store and retrieve values in the format you want.

I want a single column

$count = DB::column('SELECT COUNT(*) FROM `user`);

I want an array(key => value) results (i.e. for making a selectbox)

$pairs = DB::pairs('SELECT `id`, `username` FROM `user`);

I want a single row result

$user = DB::row('SELECT * FROM `user` WHERE `id` = ?', array($user_id));

I want an array of results

$banned_users = DB::fetch('SELECT * FROM `user` WHERE `banned` = ?', array(TRUE));

NOTE: This answer was originally posted at StackOverflow.com by Xeoncross

I use three different ways to prevent my web application from being vulnerable to SQL injection.

1. Use of mysql_real_escape_string(), which is a pre-defined function in PHP, and this code add backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a. Pass the input values as parameters to minimize the chance of SQL injection.

2. Use of MySQLi.

3. The most advanced way is to use PDOs.

I hope this will help you.

NOTE: This answer was originally posted at StackOverflow.com by Soumalya Banerjee

There are many ways of preventing SQL injections and other SQL hacks. You can easily find it on the Internet (Google Search). Of course PDO is one of the good solution. But I would like to suggest you some good links prevention from SQL Injection.

What is SQL injection and how to prevent

PHP manual for SQL injection

Microsoft explanation of SQL injection and prevention in PHP

and some other like Preventing SQL injection with MySQL and PHP

Now, why you do you need to prevent your query from SQL injection?

I would like to let you know: Why do we try for preventing SQL injection with a short example below:

Query for login authentication match:

$query="select * from users where email='".$_POST['email']."' and password='".$_POST['password']."' ";

Now, if someone (a hacker) puts

$_POST['email']= admin@emali.com' OR '1=1

and password anything….

The query will be parsed in the system only upto:

$query="select * from users where email='admin@emali.com' OR '1=1';

The other part will be discarded. So, what will happen? A non-authorized user (hacker) will be able to login as admin without having his password. Now, he can do anything what admin/email person can do. See, it’s very dangerous if SQL injection is not prevented.

NOTE: This answer was originally posted at StackOverflow.com by Manish Shrivastava

Why are you all reinventing the wheel? A simple way to develop is to use some stable and reliable framework such as Joomla, Drupal or ZendFramework. The choice will rely on your needs and only you can select the appropriate one. If you just start using PHP, maybe it’s more difficult to choose because you need to have some basic knowledge about design patterns…but if you are experimented developer, you should already know that!

NOTE: This answer was originally posted at StackOverflow.com by Yoong Kim

Using this PHP function mysql_escape_string() you can get a good prevention in a fast way.

For example:

SELECT * FROM users WHERE name = '".mysql_escape_string($name_from_html_form)."'

mysql_escape_string — Escapes a string for use in a mysql_query

For more prevention you can add at the end …

wHERE 1=1   or  LIMIT 1

Finally you get:

SELECT * FROM users WHERE name = '".mysql_escape_string($name_from_html_form)."' LIMIT 1

NOTE: This answer was originally posted at StackOverflow.com by Nicolas Finelli

Parameterized query AND input validation is the way to go. There is many scenarios under which SQL injection may occur, even though mysql_real_escape_string() has been used.

Those examples are vulnerable to SQL injection :

$offset = isset($_GET['o']) ? $_GET['o'] : 0;
$offset = mysql_real_escape_string($offset);
RunQuery("SELECT userid, username FROM sql_injection_test LIMIT $offset, 10");

or

$order = isset($_GET['o']) ? $_GET['o'] : 'userid';
$order = mysql_real_escape_string($order);
RunQuery("SELECT userid, username FROM sql_injection_test ORDER BY `$order`");

In both case you can’t use ‘ to protect the encapsulation.

source : The Unexpected SQL Injection (When Escaping Is Not Enough)

NOTE: This answer was originally posted at StackOverflow.com by Cedric

Injection Prevention – mysql_real_escape_string()

PHP has a specially-made function to prevent these attacks. All you need to do is use the mouthful of a function mysql_real_escape_string.

What mysql_real_escape_string does is take a string that is going to be used in a MySQL query and return the same string with all SQL Injection attempts safely escaped. Basically, it will replace those troublesome quotes(‘) a user might enter with a MySQL-safe substitute, an escaped quote \’.

/NOTE: you must be connected to the database to use this function!
// connect to MySQL

$name_bad = "' OR 1'"; 

$name_bad = mysql_real_escape_string($name_bad);

$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";
echo "Escaped Bad Injection: <br />" . $query_bad . "<br />";


$name_evil = "'; DELETE FROM customers WHERE 1 or username = '"; 

$name_evil = mysql_real_escape_string($name_evil);

$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";
echo "Escaped Evil Injection: <br />" . $query_evil;

you can find more detail here

http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php

NOTE: This answer was originally posted at StackOverflow.com by rahularyansharma

I always use DATETIME fields for anything other than row metadata (date created or modified).

As mentioned in the MySQL documentation:

The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format. The supported range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

…

The TIMESTAMP data type has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC. It has varying properties, depending on the MySQL version and the SQL mode the server is running in.

You’re quite likely to hit the lower limit on TIMESTAMPs in general use — e.g. storing birthdate.

NOTE: This answer was originally posted at StackOverflow.com by scronide

From my experiences If you want a date field in which insertion happens only once and u don’t want o have update or any other action on that particular field go with date time .

For example in a user table REGISTRATION DATE filed.

In that user table if u want to know the last logged in time of a particular user go with a filed of timestamp type let that filed updated with a trigger.

NOTE: This answer was originally posted at StackOverflow.com by Kannan Prasad

Not sure if this has been mentioned already, but worth noting in MySQL you can use something along the lines of below when creating your table columns

on update CURRENT_TIMESTAMP

This will update the time each instance you modify a row, sometimes very helpful for stored last edit info. This only works with timestamp, not datetime however.

NOTE: This answer was originally posted at StackOverflow.com by leejmurphy

I always use a UNIX timestamp, simply to maintain sanity when dealing with a lot of datetime info, especially when performing adjustments for timezones, adding/subtracting dates, and the like. When comparing timestamps, this excludes the complicating factors of timezone and allows you to spare resources in your server side processing (Whether it be application code or database queries) in that you make use of light weight arithmetic rather then heavier date-time add/subtract functions.

NOTE: This answer was originally posted at StackOverflow.com by Oliver Holmberg

The main difference is that DATETIME is constant while TIMESTAMP is effected by the time_zone setting.

So it only matters when you have – or may in the future have – synchronized clusters across time zones.

In simpler words: If I have a database in Australia, and take a dump of that database to synchronize/populate a database in America, then the TIMESTAMP would update to reflect the real time of the event in the new time zone, while DATETIME would still reflect the time of the event in the au time zone.

A great example of DATETIME being used where TIMESTAMP should have been used is in Facebook, where their servers are never quite sure what time stuff happened across time zones.

Once I was having a conversation in which the time said I was replying to messages before the message was actually sent.

This of course could also have been caused by bad time zone translation in the messaging software if the times were being posted rather than synchronized.

NOTE: This answer was originally posted at StackOverflow.com by ekerner

I make this decision on a semantic base.

I use a timestamp when I need to record a (more or less) fixed point in time. For example when a record was inserted into the database or when some user action took place.

I use a datetime field when the date/time can be set and changed arbitrarily. For example when a user can save later change appointments.

NOTE: This answer was originally posted at StackOverflow.com by unbeknown

I recommend PHP Simple HTML DOM Parser
it really has nice features like

foreach($html->find('img') as $element) 
       echo $element->src . '<br>';

NOTE: This answer was originally posted at StackOverflow.com by user1090298

phpQuery and QueryPath are extremely similar in replicating the fluent jQuery API. That’s also why they’re one of the easiest approaches to properly parse HTML in PHP.

Examples for QueryPath

Basically you first create a queryable DOM tree from a HTML string:

 $qp = qp("<html><body><h1>title</h1>..."); // or give filename or URL

The resulting object contains a complete tree representation of the HTML document. It can be traversed using DOM methods. But the common approach is to use CSS selectors like in jQuery:

 $qp->find("div.classname")->children()->...;

 foreach ($qp->find("p img") as $img) {
     print qp($img)->attr("src");
 }

Mostly you want to use simple #id and .class or DIV tag selectors for ->find(). But you can also use xpath statements, which sometimes are faster. Also typical jQuery methods like ->children() and ->text() and particularily ->attr() simplify extracting the right HTML snippets. (And already have their SGML entities decoded.)

 $qp->xpath("//div/p[1]");  // get first paragraph in a div

QueryPath also allows injecting new tags into the stream (->append), and later output and prettify an updated document (->writeHTML). It can not only parse malformed HTML, but also various XML dialects (with namespaces), and even extract data from HTML microformats (XFN, vCard).

 $qp->find("a[target=_blank]")->toggleClass("usability-blunder");

.

phpQuery or QueryPath?

Generally QueryPath is better suited for manipulation of documents. While phpQuery also implements some pseudo AJAX methods (just HTTP requests) to more closely resemble jQuery. It is said that phpQuery is often faster than QueryPath (because overall less features).

For further informations on the differences see this comparison:
http://web.archive.org/web/20101230230134/http://www.tagbytag.org/articles/phpquery-vs-querypath (Original source went missing, so here’s an internet archive link. Yes, you can still locate missing pages, people.)

And here’s a comprehensive QueryPath introduction: http://www.ibm.com/developerworks/opensource/library/os-php-querypath/index.html?S_TACT=105AGX01&S_CMP=HP

Advantages

• Simplicity and Reliability
• Simple to use alternatives ->find(“a img, a object, div a”)
• Proper data unescaping (in comparison to regular expression greping)

NOTE: This answer was originally posted at StackOverflow.com by mario

There is also Goutte (PHP Web Scraper) which is now available :
https://github.com/fabpot/Goutte/

NOTE: This answer was originally posted at StackOverflow.com by Shal

Try the Simple HTML Dom Parser:

// Create DOM from URL or file
$html = file_get_html('http://www.example.com/');

// Find all images 
foreach($html->find('img') as $element) 
       echo $element->src . '<br>';

// Find all links 
foreach($html->find('a') as $element) 
       echo $element->href . '<br>';

NOTE: This answer was originally posted at StackOverflow.com by NAVEED

Yes you can use simple_html_dom for the purpose. However I have worked quite a lot with the simple_html_dom, particularly for web scrapping and have found it to be too vulnerable. It does the basic job but I won’t recommend it anyways.

I have never used curl for the purpose but what I have learned is that curl can do the job much more efficiently and is much more solid.

Kindly check out this link: http://spyderwebtech.wordpress.com/2008/08/07/scraping-websites-with-curl/

NOTE: This answer was originally posted at StackOverflow.com by Spoilt

we have created quite a few crawlers for our needs before. at the end of the day, it is usually simple regular expressions that do the thing best. while libraries listed above are good for the reason they are created, if you know what you are looking for, regular expressions is more safe way to go, as you can handle also non-valid html/xhtml structures, which would fail, if loaded via most of the parsers.

NOTE: This answer was originally posted at StackOverflow.com by jancha