mysql_query
I always use this function so I don't have to retype over and over the mysql_real_escape_string function.
<?php
function safe($value){
return mysql_real_escape_string($value);
}
?>
Then, when I am using my code, I simply use:
<?php
$name = safe($_POST["name"]);
$password = safe($_POST["password"]);
?>
mysql_real_escape_string
(PHP 4 >= 4.3.0, PHP 5, PECL mysql:1.0)
mysql_real_escape_string — 转义 SQL 语句中使用的字符串中的特殊字符，并考虑到连接的当前字符集
说明
string mysql_real_escape_string
( string $unescaped_string
[, resource $link_identifier
] )
本函数将 unescaped_string 中的特殊字符转义，并计及连接的当前字符集，因此可以安全用于 mysql_query()。
Note: mysql_real_escape_string() 并不转义 % 和 _。
Example#1 mysql_real_escape_string() 例子
<?php
$item = "Zak's and Derick's Laptop";
$escaped_item = mysql_real_escape_string($item);
printf ("Escaped string: %s\n", $escaped_item);
?>
以上例子将产生如下输出：
Escaped string: Zak\'s and Derick\'s Laptop
参见 mysql_escape_string() 和 mysql_character_set_name()。
add a note
User Contributed Notesmysql_real_escape_string
info at saturnprods dot com
14-Jun-2009 03:37
14-Jun-2009 03:37
kendsnyder at gmail dot com
26-Mar-2009 05:07
26-Mar-2009 05:07
<?php
// Here is a simple named binding function for queries that makes SQL more readable:
// $sql = "SELECT * FROM users WHERE user = :user AND password = :password";
// mysql_bind($sql, array('user' => $user, 'password' => $password));
// mysql_query($sql);
function mysql_bind(&$sql, $vals) {
foreach ($vals as $name => $val) {
$sql = str_replace(":$name", "'" . mysql_real_escape_string($val) . "'", $sql);
}
}
?>
Bastiaan Welmers
25-Mar-2008 03:46
25-Mar-2008 03:46
This function won't help you when inserting binary data, to me it will get mallformed into the database. Probably UTF-8 combinations will be translated by this function or somewhere else when inserting data when running mysql in UTF-8 mode.
A better way to insert binary data is to transfer it to hexadecimal notation like this example:
<?php
$string = $_REQUEST['string'];
$binary = file_get_contents($_FILE['file']['tmp_name']);
$string = mysql_real_escape_string($string);
$binary_hex = bin2hex($binary);
$query = "INSERT INTO `table` (`key`, `string`, `binary`, `other`) VALUES (NULL, '$string', 0x$binary_hex, '$other')";
?>
Anonymous
03-Mar-2008 02:57
03-Mar-2008 02:57
My escape function:
Automatically adds quotes (unless $quotes is false), but only for strings. Null values are converted to mysql keyword "null", booleans are converted to 1 or 0, and numbers are left alone. Also can escape a single variable or recursively escape an array of unlimited depth.
<?php
function db_escape($values, $quotes = true) {
if (is_array($values)) {
foreach ($values as $key => $value) {
$values[$key] = db_escape($value, $quotes);
}
}
else if ($values === null) {
$values = 'NULL';
}
else if (is_bool($values)) {
$values = $values ? 1 : 0;
}
else if (!is_numeric($values)) {
$values = mysql_real_escape_string($values);
if ($quotes) {
$values = '"' . $values . '"';
}
}
return $values;
}
?>
matthijs at yourmediafactory dot com
27-Dec-2007 05:49
27-Dec-2007 05:49
In response to Michael D - DigitalGemstones.com:
Check the example again: sprintf(%d) already does the int conversion for you, so it's both perfectly save as well as more elegant than manually casting.
user at NOSPAM dot example dot com
28-Aug-2007 08:16
28-Aug-2007 08:16
if you're doing a mysql wildcard query with
LIKE, GRANT, or REVOKE
you may use addcslashes to escape the string:
<?php
$param = mysql_real_escape_string($param);
$param = addcslashes($param, '%_');
?>
brian dot folts at gmail dot com
07-Sep-2006 12:25
07-Sep-2006 12:25
mysql_real_escape_string is a bit annoying when you need to do it over an array.
<?php
function mysql_real_escape_array($t){
return array_map("mysql_real_escape_string",$t);
}
?>
this one just mysql_real_escape's the whole array.
ex) <?php $_POST=mysql_real_escape_array($_POST); ?>
and then you dont have to worry about forgetting to do this.
kael dot shipman at DONTSPAMIT! dot gmail dot com
19-Jul-2006 04:19
19-Jul-2006 04:19
It seems to me that you could avoid many hassels by loading valid database values into an array at the beginning of the script, then instead of using user input to query the database directly, use it to query the array you've created. For example:
<?php
//you still have to query safely, so always use cleanup functions like eric256's
$categories = sql_query("select catName from categories where pageID = ?",$_GET['pageID']);
while ($cts = @mysql_fetch_row($categories)) {
//making $cts both the name and the value of the array variable makes it easier to check for in the future.
//obviously, this naming system wouldn't work for a multidimensional array
$cat_ar[$cts[0]] = $cts[0];
}
...
//user selects sorting criteria
//this would be from a query string like '?cats[]=cha&cats[]=fah&cats[]=lah&cats[]=badValue...', etc.
$cats = $_GET['cats'];
//verify that values exist in database before building sorting query
foreach($cats as $c) {
if ($cat_ar[$c]) { //instead of in_array(); maybe I'm just lazy... (see above note)
$cats1[] = "'".mysql_real_escape_string($c)."'";
}
}
$cats = $cats1;
//$cats now contains the filtered and escaped values of the query string
$cat_query = '&& (category_name = \''.implode(' || category_name = \'',$cats).'\')';
//build a sql query insert
//$cat_query is now "&& (category_name = 'cha' || category_name = 'fah' || category_name = 'lah')" - badValue has been removed
//since all values have already been verified and escaped, you can simply use them in a query
//however, since $pageID hasn't been cleaned for this query, you still have to use your cleaning function
$items = sql_query("SELECT * FROM items i, categories c WHERE i.catID = c.catID && pageID = ? $cat_query", $pageID);
nicolas
31-May-2006 04:38
31-May-2006 04:38
Note that mysql_real_escape_string doesn't prepend backslashes to \x00, \n, \r, and and \x1a as mentionned in the documentation, but actually replaces the character with a MySQL acceptable representation for queries (e.g. \n is replaced with the '\n' litteral). (\, ', and " are escaped as documented) This doesn't change how you should use this function, but I think it's good to know.