CSC220 Preventing SQL Injections in Php

--D. Thiebaut 18:39, 31 October 2010 (UTC)

---

Some Php Code

<?php
// run this program to se how mysql_real_escape_string() will change
// the quotes inside a string to prevent injection in an SQL query.

include "accessinfo.php";  //mysql_rea_escape_string requires a live connection to a database

function demo( $string ) {
  print "------------------------------------------------------------------\n";
  print "string = $string\n";
  print "mysql_real_escape_string( string ) = " . mysql_real_escape_string( $string ) . "\n\n";
  print "SELECT `someField` FROM `someTable` WHERE `fieldName`='$string';" . "\n";
  print "SELECT `someField` FROM `someTable` WHERE `fieldName`='" . mysql_real_escape_string( $string)  . "';" . "\n\n";
  }

$link = mysql_connect( $hostName, $userName, $password );
$selected = mysql_select_db( $database, $link );

demo( "O'Reilly" );

demo( "anything' OR 'x'='x" );

?>

Output

------------------------------------------------------------------
string = O'Reilly
mysql_real_escape_string( string ) = O\'Reilly

SELECT `someField` FROM `someTable` WHERE `fieldName`='O'Reilly';
SELECT `someField` FROM `someTable` WHERE `fieldName`='O\'Reilly'; 

------------------------------------------------------------------
string = anything' OR 'x'='x 
mysql_real_escape_string( string ) = anything\' OR \'x\'=\'x

SELECT `someField` FROM `someTable` WHERE `fieldName`='anything' OR 'x'='x';
SELECT `someField` FROM `someTable` WHERE `fieldName`='anything\' OR \'x\'=\'x';