So, I am trying to use the get method while protecting from injections. I'm trying to get data from the database and echo it out to a page. I think it's pretty obvious what I'm trying to do with the code below but i need help with using the right syntax.
Can someone show me the right syntax for the prepare statement to get data from a database using mysqli that is protected from injections?
I've looked on this site can't seem to find what I'm looking for and the PHP site I couldn't find an up to date method. Thanks for all the help.
<?php
$mysqli = new mysqli("", "", "", "");
if ($mysqli->connect_error) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_error . ") " . $mysqli->connect_error;
}
$stmt = $mysqli->stmt_init();
if($stmt->prepare("SELECT 'name,name' FROM 'table' WHERE 'name, name' = ?,?")) {
}
if (!$stmt->bind_param('si', $_GET['name'], $_GET['name'])); {
echo "Binding parameters failed: (" . $stmt->error . ") " . $stmt->error;
}
if (!$stmt->execute()) {
echo "Execute failed: (" . $stmt->error . ") " . $stmt->error;
}
if (!$stmt->fetch()); {
echo "Binding parameters failed: (" . $stmt->error . ") " . $stmt->error;
}
$stmt->close();
?>
your sql is wrong:
if($stmt->prepare("SELECT 'name,name' FROM 'table' WHERE 'name, name' = ?,?")) {
must be
if($stmt->prepare("SELECT name, name FROM table WHERE name=? AND name=? ")) {
Double the expressive name, I used only because of the question.
The following is clearer:
if($stmt->prepare("SELECT astring, ainteger FROM table WHERE astring=? AND ainteger=? "))
{
if (!$stmt->bind_param('si', $_GET['astring'], $_GET['ainteger'])) {
Take out some time to write the question carefully. If two variables are used, then designate different, everything else just confused.
Update :
- Before you use
bind_param()
- You have to test all $_GET["xx"].
if (isset($_GET['name']))
- When you call a function, terminated with
; for example:
if (!$stmt->bind_param('si', $_GET['name'], $_GET['name'])); {
Then the curly braces are useless, no matter the if gets true or false!
The following code after if (!$stmt->bind_param(...)); will always be executed, because the command, is finished.
if (!$stmt->bind_param('si', $_GET['name'], $_GET['name'])); {
echo "Binding parameters failed: (" . $stmt->error . ") " . $stmt->error;
}
It took a long time until I found this error. It is easily overlooked.
That's why you always get your own error messages.
to protect from sql injection, you should first make a connection to your mysql database and after that you should surround your $_GET with mysql_real_escape_string(), like this:
mysql_real_escape_string($_GET['name'])
or to use the newer function
mysqli_real_escape_string($_GET['name'])
This is better solution if you whant to protect all GET inputs:
function GET($name=NULL, $value=false)
{
$content=(!empty($_GET[$name]) ? trim($_GET[$name]) : (!empty($value) && !is_array($value) ? trim($value) : false));
if(is_numeric($content))
return preg_replace("@([^0-9])@Ui", "", $content);
else if(is_bool($content))
return ($content?true:false);
else if(is_float($content))
return preg_replace("@([^0-9\,\.\+\-])@Ui", "", $content);
else if(is_string($content))
{
if(filter_var ($content, FILTER_VALIDATE_URL))
return $content;
else if(filter_var ($content, FILTER_VALIDATE_EMAIL))
return $content;
else if(filter_var ($content, FILTER_VALIDATE_IP))
return $content;
else if(filter_var ($content, FILTER_VALIDATE_FLOAT))
return $content;
else
return preg_replace("@([^a-zA-Z0-9\+\-\_\*\@\$\!\;\.\?\#\:\=\%\/\ ]+)@Ui", "", $content);
}
else false;
}
Just instead $_GET['something'] you use GET('something') and you have option to put default value if GET value don't exists. And lather in MySQL query you can use escape string or prepared state to full protect your query.
