DB Connection in PHP

Jeyaseelansarc · #21 (**permalink**) 04-11-2008, 02:05 AM

This simple example shows how to connect, execute a query, print resulting rows and disconnect from a MySQL database.

PHP Code:

  <?php

// Connecting, selecting database

$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')

    or die('Could not connect: ' . mysql_error());

echo 'Connected successfully';

mysql_select_db('my_database') or die('Could not select database');

 
// Performing SQL query

$query = 'SELECT * FROM my_table';

$result = mysql_query($query) or die('Query failed: ' . mysql_error());

 
// Printing results in HTML

echo "<table>\n";

while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {

    echo "\t<tr>\n";

    foreach ($line as $col_value) {

        echo "\t\t<td>$col_value</td>\n";

    }

    echo "\t</tr>\n";

}

echo "</table>\n";

 
// Free resultset

mysql_free_result($result);

 
// Closing connection

mysql_close($link);

?>

Jeyaseelansarc · #22 (**permalink**) 04-11-2008, 02:06 AM

mysql_change_user() changes the logged in user of the current active connection, or the connection given by the optional link_identifier parameter. If a database is specified, this will be the current database after the user has been changed. If the new user and password authorization fails, the current connected user stays active.

Jeyaseelansarc · #23 (**permalink**) 04-11-2008, 02:08 AM

mysql_client_encoding() retrieves the character_set variable from MySQL.

Example:

PHP Code:

  <?php

$link    = mysql_connect('localhost', 'mysql_user', 'mysql_password');

$charset = mysql_client_encoding($link);

 
echo "The current character set is: $charset\n";

?>

the output as

Code:

The current character set is: latin1

Jeyaseelansarc · #24 (**permalink**) 04-11-2008, 02:09 AM

mysql_real_escape_string() escapes special characters in the unescaped_string, taking into account the current character set of the connection so that it is safe to place it in a mysql_query(). If binary data is to be inserted, this function must be used.

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

This function must always (with few exceptions) be used to make data safe before sending a query to MySQL.

Jeyaseelansarc · #25 (**permalink**) 04-11-2008, 02:10 AM

Simple mysql_real_escape_string() example

PHP Code:

  <?php

// Connect

$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')

    OR die(mysql_error());

 
// Query

$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",

            mysql_real_escape_string($user),

            mysql_real_escape_string($password));

?>

Jeyaseelansarc · #26 (**permalink**) 04-11-2008, 02:11 AM

Using mysql_real_escape_string() around each variable prevents SQL Injection. This example demonstrates the "best practice" method for querying a database, independent of the Magic Quotes setting.

PHP Code:

  <?php

// Quote variable to make safe

function quote_smart($value)

{

    // Stripslashes

    if (get_magic_quotes_gpc()) {

        $value = stripslashes($value);

    }

    // Quote if not integer

    if (!is_numeric($value)) {

        $value = "'" . mysql_real_escape_string($value) . "'";

    }

    return $value;

}

 
// Connect

$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')

    OR die(mysql_error());

 
// Make a safe query

$query = sprintf("SELECT * FROM users WHERE user=%s AND password=%s",

            quote_smart($_POST['username']),

            quote_smart($_POST['password']));

 
mysql_query($query);

?>

Jeyaseelansarc · #27 (**permalink**) 04-11-2008, 02:12 AM

An example SQL Injection Attack

PHP Code:

  <?php

// Query database to check if there are any matching users

$query = "SELECT * FROM users WHERE user='{$_POST['username']}' AND password='{$_POST['password']}'";

mysql_query($query);

 
// We didn't check $_POST['password'], it could be anything the user wanted! For example:

$_POST['username'] = 'aidan';

$_POST['password'] = "' OR ''='";

 
// This means the query sent to MySQL would be:

echo $query;

?>

The query sent to MySQL as:

Code:

SELECT * FROM users WHERE name='aidan' AND password='' OR ''=''

This would allow anyone to log in without a valid password.

Jeyaseelansarc · #28 (**permalink**) 04-11-2008, 02:13 AM

A MySQL connection is required before using mysql_real_escape_string() otherwise an error of level E_WARNING is generated, and FALSE is returned. If link_identifier isn't defined, the last MySQL connection is used.

Jeyaseelansarc · #29 (**permalink**) 04-11-2008, 02:14 AM

If magic_quotes_gpc is enabled, first apply stripslashes() to the data. Using this function on data which has already been escaped will escape the data twice.

Jeyaseelansarc · #30 (**permalink**) 04-11-2008, 02:15 AM

If this function is not used to escape data, the query is vulnerable to SQL Injection Attacks.

Jeyaseelansarc · #31 (**permalink**) 04-11-2008, 02:16 AM

mysql_real_escape_string() does not escape % and _. These are wildcards in MySQL if combined with LIKE, GRANT, or REVOKE.

Jeyaseelansarc · #32 (**permalink**) 04-14-2008, 03:17 AM

mysql_data_seek() moves the internal row pointer of the MySQL result associated with the specified result identifier to point to the specified row number. The next call to mysql_fetch_row() would return that row.

Jeyaseelansarc · #33 (**permalink**) 04-14-2008, 03:17 AM

mysql_data_seek() example

PHP Code:

  <?php

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');

if (!$link) {

    die('Could not connect: ' . mysql_error());

}

$db_selected = mysql_select_db('sample_db');

if (!$db_selected) {

    die('Could not select database: ' . mysql_error());

}

$query = 'SELECT last_name, first_name FROM friends';

$result = mysql_query($query);

if (!$result) {

    die('Query failed: ' . mysql_error());

}

/* fetch rows in reverse order */

for ($i = mysql_num_rows($result) - 1; $i >= 0; $i--) {

    if (!mysql_data_seek($result, $i)) {

        echo "Cannot seek to row $i: " . mysql_error() . "\n";

        continue;

    }

 
    if (!($row = mysql_fetch_assoc($result))) {

        continue;

    }

 
    echo $row['last_name'] . ' ' . $row['first_name'] . "<br />\n";

}

 
mysql_free_result($result);

?>

Jeyaseelansarc · #34 (**permalink**) 04-14-2008, 03:19 AM

mysql_escape_string () will escape the unescaped_string, so that it is safe to place it in a mysql_query(). This function is deprecated.

This function is identical to mysql_real_escape_string() except that mysql_real_escape_string() takes a connection handler and escapes the string according to the current character set. mysql_escape_string() does not take a connection argument and does not respect the current charset setting.

Jeyaseelansarc · #35 (**permalink**) 04-14-2008, 03:21 AM

mysql_escape_string() example

PHP Code:

  <?php

$item = "Zak's Laptop";

$escaped_item = mysql_escape_string($item);

printf("Escaped string: %s\n", $escaped_item);

?>

the output of the above

Code:

Escaped string: Zak\'s Laptop

mysql_escape_string() does not escape % and _.

Jeyaseelansarc · #36 (**permalink**) 04-15-2008, 01:44 AM

mysql_field_flags() returns the field flags of the specified field. The flags are reported as a single word per flag separated by a single space, so that you can split the returned value using explode().

Example:

PHP Code:

  <?php
$result = mysql_query("SELECT id,email FROM people WHERE id = '42'");
if (!$result) {
    echo 'Could not run query: ' . mysql_error();
    exit;
}
$flags = mysql_field_flags($result, 0);

echo $flags;
print_r(explode(' ', $flags));
?>

the output as

Code:

not_null primary_key auto_increment
Array
(
    [0] => not_null
    [1] => primary_key
    [2] => auto_increment
)

Jeyaseelansarc · #37 (**permalink**) 04-15-2008, 01:47 AM

mysql_field_len() returns the length of the specified field.

Example:

PHP Code:

  <?php

$result = mysql_query("SELECT id,email FROM people WHERE id = '42'");

if (!$result) {

    echo 'Could not run query: ' . mysql_error();

    exit;

}

 
// Will get the length of the id field as specified in the database

// schema. 

$length = mysql_field_len($result, 0);

echo $length;

?>

Jeyaseelansarc · #38 (**permalink**) 04-15-2008, 01:48 AM

mysql_field_name() returns the name of the specified field index.

Example:

PHP Code:

  <?php

/* The users table consists of three fields:

*   user_id

*   username

*   password.

*/

$link = @mysql_connect('localhost', 'mysql_user', 'mysql_password');

if (!$link) {

    die('Could not connect to MySQL server: ' . mysql_error());

}

$dbname = 'mydb';

$db_selected = mysql_select_db($dbname, $link);

if (!$db_selected) {

    die("Could not set $dbname: " . mysql_error());

}

$res = mysql_query('select * from users', $link);

 
echo mysql_field_name($res, 0) . "\n";

echo mysql_field_name($res, 2);

?>

the output as:

Code:

user_id
password