PHP

Preventing SQL Injection

Another atatck is the SQL injection attack, i.e. a bad code is inserted into a site's SQL queries. One aim of such attacks is that they would create a syntactically invalid query, thereby revealing something about the script or database in the resulting error message. Another aim is that the injection attacks could alter, destroy, or expose the stored data. So do not show detailed errors on live sites.

There are 3 ways of preventing such attacks:

Validate all queries to be used in queries and performtypecasting whenever possible.
Use mysql_real_escape_string() which makes data safe to use in queries.
Use prepared statements (only in PHP 5). With a prepared query, the SQL syntax is sent to MySQL first, where it is parsed, making sure it's syntactically valid. Then the specific values are sent separately; MySQL assembles the query using those values, and executes it.

Here are the bound value types

Letter	Represents
d	Decimal
i	Integer
b	Blob (binary data)
s	All other types

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="EN" lang="EN">
<head>
<title>Preventing SQL Injection</title>
<meta Name="Author" Content="Hann So">
</head>
<body>
<p>
<?php

if (isset($_POST['submit'])) {
		process_form();
	}
	else {
		display_form();// display form for the first time
	}

function display_form() {
	echo <<<HTML
	<h2>Preventing SQL Injection</h2>
	<form action = "$_SERVER[SCRIPT_NAME]" method="post">
	Title:
	<input type="text" name="title" size="50" value="First blog" />
	<br />
	Entry Text:
	<textarea name="entry" cols="40" rows="5">This is my first blog</textarea>
	<br />
	<input type="submit" name="submit" value="Submit" />
	</form>
HTML;
}

function process_form() {

	// connect to the database server
	include ('db_connect.php');

	// define the query
	// NOW() inserts the current time
	// use the prepared statement with ? for values
	$query = "INSERT INTO " . TABLE_NAME . " (
	title, entry, date_entered)
	VALUES (?, ?, NOW())";

	// prepare the statement (mysqli works for PHP 5 only)
	$statement = mysqli_prepare($link,$query);

	// bind the variables
	mysqli_stmt_bind_param($statement, 'ss', $title, $entry);

	// assign the values to variables
	// remove any potentially dangerous code
	$title = strip_tags($_POST['title']);
	$entry = strip_tags($_POST['entry']);

	// execute the statement
	mysqli_stmt_execute($statement);
	// print a message
	if (mysqli_stmt_affected_rows($statement) == 1) {
		echo "<p>Your blog has been posted</p>";
	}
	else {
		echo "<p>Your blog could not be posted</p>";
	}

	// close the statement
	mysqli_stmt_close($statement);
	// close the connection
	mysql_close($link);

	echo "<p><a href=\"$_SERVER[SCRIPT_NAME]\">Try again?</a></p>\n";

}
?>
</p>
</body>
</html>

View the effect