How to prevent MySQL injection attacks?

November 13th 2010 18:50

1. What is MySQ injection?

SQL injection refers to the act of someone inserting a MySQL statement to be run on your database without your knowledge. Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a MySQL statement that you will unknowingly run on your database.

2. Example of MySQL injection.

Suppose we have SQL code:

----------------------------------------------

SELECT fieldlist
  FROM table
 WHERE field = '$EMAIL';

--------------------------------------

By entering EMAIL as "anything' OR 'x'='x",
the resulting SQL is:

-----------------------------------------------

SELECT fieldlist
  FROM table
 WHERE field = 'anything' OR 'x'='x';

------------------------------------------

'x'='x' clause is guaranteed to be true no matter what the first clause is.

This OR clause of 1 will always be true and so every single entry in the "customers" table would be selected by this statement!

3. How to prevent MySQL injection attacks?

Injection Prevention - mysql_real_escape_string()

What mysql_real_escape_string does is to 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 \'.

prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

Note: mysql_real_escape_string function

only works if you are already connected to a database.

Example #1 Simple mysql_real_escape_string() example

 $link = mysql_connect('mysql_host', 
'mysql_user', 'mysql_password')
    OR die(mysql_error());
$query = sprintf("SELECT *  
FROM users WHERE user='%s' AND password='%s'",
            mysql_real_escape_string($user),
            mysql_real_escape_string($password));

?>

Jelly Fish

Vote

Add Comments

[ Link ]

Posted By: Jiansen Lu - Category: No Category

Email this Post to a Friend

Display MySQL output in tables using PHP

November 12th 2010 04:41

Below are functions in PHP/MySQL to use for this example:

mysql_connect - connects to MySQL server
mysql_select_db - select database
mysql_query - send query
mysql_fetch_row - get current row from result table
mysql_num_fields - get number of fields
mysql_fetch_field - get field information
mysql_free_result - free result table from memory

<html><head><title>MySQL Table Example</title></head><body>
<?php
$db_host = 'localhost';
$db_user = 'root';
$db_pwd = 'mypasswd';

$database = 'jiansen_db';
$table = 'user_table';

if (!mysql_connect($db_host, $db_user, $db_pwd))
    die("Can't connect to database");

if (!mysql_select_db($database))
    die("Can't select database");

// sending query
$result = mysql_query("SELECT * FROM {$table}");
if (!$result) {
    die("Query to show fields from table failed");
}

$fields_num = mysql_num_fields($result);

echo "<h1>Table: {$table}</h1>";
echo "<table border='1'><tr>";
// printing table headers
 for($i=0; $i<$fields_num; $i  )
{
    $field = mysql_fetch_field($result);
    echo "<td>{$field->name}</td>";
}
echo "</tr>\n";
// printing table rows
while($row = mysql_fetch_row($result))
{
    echo "<tr>";

    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
    foreach($row as $cell)
        echo "<td>$cell</td>";

    echo "</tr>\n";
}
mysql_free_result($result);
?>
</body></html>

Vote

Add Comments

[ Link ]

Posted By: Jiansen Lu - Category: No Category

Email this Post to a Friend

This Forum Format

November 11th 2010 05:21

Modify:
Format for blog: Single Forum left
Format for post: Default
setting:
Show full text of posts on Blog Home Page: 5
My Blogger blog:
My orble blog

Vote

Add Comments

[ Link ]

Posted By: Jiansen Lu - Category: No Category

Email this Post to a Friend