How can I allow special characters like " ' \ / : ; etc without open up for SQL injection using the code below:
$opendb = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname);
$text = $_POST['text'];
mysql_query("UPDATE table SET text='" . $text . "' WHERE
id='" . $_GET['id'] . "'");
mysql_close($opendb);
$text contains a sentence from a HTML textarea. When I tries to enter text in a quote it just insert the text before the quotes.
This would be the safest way to go about doing this. Check out this link for more: How can I prevent SQL injection in PHP?
You might also need to turn off magic quotes, depending what PHP version you are running.
<?php
if( isset($_POST['text']) && isset($_GET['id']) &&
is_int($_GET['id']) && $_GET['id']>0 ){
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$query = 'UPDATE table SET text = ? WHERE id = ?';
/* prepare your statement safely */
$stmt = $mysqli->prepare($query);
/* bindes variables after statement is prepared */
$stmt->bind_param('si', $_POST['text'], $_GET['id']);
/* execute prepared statement */
$stmt->execute();
/* close statement */
$stmt->close();
/* close connection */
$mysqli->close();
}else
echo 'Error: ID and/or Text are invalid';
?>
Well, maybe the simplest solution is to use mysql_real_escape_string() function like this:
$opendb = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname);
$text = $_POST['text'];
mysql_query("UPDATE table SET text='" . mysql_real_escape_string($text) . "' WHERE
id='" . $_GET['id'] . "'");
mysql_close($opendb);
Edit: using this code you could allow special characters in $text variable to be saved into the database.
You should escape $_GET['id'] also.
