Difference between revisions of "MySQL Access from PHP"

Revision as of 22:08, 23 May 2007

QUICK EXAMPLE

 $db_hostname = "localhost";
 $db_username = "user";
 $db_password = "pass";
 $db_database = "dbase1";
 $db_table1   = "table1";
 mysql_connect($db_hostname, $db_username, $db_password) or die("Could not connect");
 mysql_select_db($db_database);
 $txtQuery1 = "SELECT * FROM " . $db_table1 . " ORDER BY id";
 $result = mysql_query($txtQuery1);
 for ($i = 0; $i < mysql_num_rows($result); $i++) {
   $row_array = mysql_fetch_row($result);
   echo ("\n<li>" . $row_array[1] );
 }
 mysql_close();

INSERT

 // Make a MySQL Connection
 mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
 mysql_select_db("test") or die(mysql_error());
 //
 // Insert a row of information into the table "example"
 mysql_query("INSERT INTO example 
 (name, age) VALUES('Timmy Mellowman', '23' ) ") 
 or die(mysql_error());  
 //
 mysql_query("INSERT INTO example 
 (name, age) VALUES('Sandy Smith', '21' ) ") 
 or die(mysql_error());  
 //
 mysql_query("INSERT INTO example 
 (name, age) VALUES('Bobby Wallace', '15' ) ") 
 or die(mysql_error());  
 //
 echo "Data Inserted!";

• Escape special characters, such as single quotes in variable for SQL insertion

 $strTitle = mysql_real_escape_string($strTitle);

• Example of escaped special character and injection safe

 <?php
 // Quote variable to make safe
 function quote_smart($value)
 {
    // Stripslashes
    if (get_magic_quotes_gpc()) {
        $value = stripslashes($value);
    }
    // Quote if not a number or a numeric string
    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);
 ?>

SQL INJECTION ATTACK PREVENTION

reference: http://www.php.net/manual/en/security.database.sql-injection.php

 if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches))
   $result = mysql_query("SELECT * FROM users WHERE username=$matches[0]");
 else // we don't bother querying the database
   echo "username not accepted";

secure way to compose a query for paging

 <?php
 settype($offset, 'integer');
 $query = "SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET $offset;";
 //
 // please note %d in the format string, using %s would be meaningless
 $query = sprintf("SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET %d;",
                $offset);
 ?>

here's what I like to use...

 // i am going to make this safer
 $searchstr = preg_replace('#[^a-zA-Z0-9_-]#', ' ', $searchstr);