MySQL Access from PHP
From Free Knowledge Base- The DUCK Project: information for everyone
Contents
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);