SQL with PHP and SQL Injection Attacks 1

Basic PHP Syntax <html> <head><title>Hello World</title></head> <body> <? php echo ‘<p>Hello, how are you?

Scalars All variables in PHP start with a $ sign symbol. A variable's type

Arrays <html> <body> <? php $arr = array("foo" => "bar", 3 => true); $arr[4]

Operators l l l Arithmetic Operators: +, -, *, / , %, ++, -Assignment

Conditionals: if else <html><head></head> <body> <? php $d=date("D"); echo "Today is ", $d, "day

Looping: while <html><head></head> <body> <? php $array = array(5, 4, 3, 2, 1); $i

Looping: for and foreach <html><head></head> <body> <? php $array = array(5, 4, 3, 2,

User Defined Functions <html><body> <? php $array = array(5, 4, 3, 2, 1); function

Server Variables The $_SERVER is a variable that contains system information <html><head></head> <body> <?

Server Variables l output: User's IP address: 123. 4. 56. 111 Referer: Browser: Mozilla/5.

Form Handling <html> <body> <form action="welcome. php" method="POST"> <p>Enter your name: <input type="text" name="name"

PHP with My. SQL l Connect to the database server and login $conn =

PHP with My. SQL l $conn = mysqli_connect("host", "user", "pwd“, “dbname"); l $result=mysqli_query($conn, "SQL

Guestbook Example <html><head><title>My Guestbook</title></head> <body><h 1>Welcome to my Guestbook</h 1> <h 2>Enter your comments

What is a SQL Injection Attack? l Wikipedia: A code injection technique that exploits

Example Incorrect password; so no rows will be returned 28

Example Oops! all rows will be returned because ‘x’ =‘x’ is always true SQL

Authorization Bypass l l l Simplest SQL injection technique is bypassing login forms Example:

Another Example $SQLQuery = “SELECT * FROM users WHERE username = ‘” + user

Another Example l Suppose your code has the following SQL statement $query = “SELECT

A More Malicious Example l What if the attacker had instead entered: Patient SSN:

Other injection possibilities l Using SQL injections, attackers can: – Add new data to

Defenses l Use provided functions for escaping strings – Many attacks can be thwarted

More Defenses l Check syntax of input for validity l Have length limits on

Slides: 39

Download presentation

SQL with PHP and SQL Injection Attacks 1

Basic PHP Syntax <html> <head><title>Hello World</title></head> <body> <? php echo ‘<p>Hello, how are you? </p>‘; ? > <p>This will be ignored by PHP. </p> <? php print(‘<p>welcome to my web site! </p>'); ? > <? php //This is a comment in PHP /* This is a comment block in PHP */ ? > </body> </html> 2

Basic PHP Syntax output: 3

Scalars All variables in PHP start with a $ sign symbol. A variable's type is determined by the context in which that variable is used <html> <body> <p> <? php $boolvar = True; if ($boolvar) echo "It is TRUE! "; $mystr='1234'; echo "String is $mystr "; $a = 1234; echo "Integer is $a "; $a = 314. 2 E-2; echo "Float is $a "; echo 'Arnold said: "I'll be back"', " "; $str = <<<EOD An example of a string that spans Multiple lines EOD; echo $str; ? > </p> </body></html> <p> line <br/> line 2<br/><p/> br: line brak, <p> paragraph. <<<EOD like “” with multiple lines. ‘ ‘ for simple strings and “” for strings with variables, etc. 4

Scalars 5

Arrays <html> <body> <? php $arr = array("foo" => "bar", 3 => true); $arr[4] = "barrr"; echo 'Array elements are '. $arr["foo"]. ', '. $arr[3]. ', '. $arr[4]. " "; echo 'Array length (before delete) is ‘. ‘, ’. count($arr). ’, ’ ' '; unset($arr[3]); // delete element 3 echo 'Array length (after delete) is ‘. ’, ’ count($arr). ’, ’. ' '; $array = array(1, 2, 3, 4, 5); print_r($array); ? > </body></html>: w 6

Arrays 7

Operators l l l Arithmetic Operators: +, -, *, / , %, ++, -Assignment Operators: =, +=, -=, *=, /=, %= Comparison Operators: ==, !=, >, <, >=, <= Logical Operators: &&, ||, ! String Operators: . , . = 8

Conditionals: if else <html><head></head> <body> <? php $d=date("D"); echo "Today is ", $d, "day "; $ctime = localtime(), true); true returns associative array if ($ctime["tm_hour"]==12) { echo "The local time now is 12: ", $ctime["tm_min"], "pm "; } else if ($ctime["tm_hour"]>12) { echo "The local time now is ", $ctime["tm_hour"]-12, ": ", $ctime["tm_min"], "pm "; } else { echo "The local time now is ", $ctime["tm_hour"], ": ", $ctime["tm_min"], "am "; } ? > </body> </html> 9

Conditionals: if else 10

Looping: while <html><head></head> <body> <? php $array = array(5, 4, 3, 2, 1); $i = 0; while ($i < 5) { echo "Element $i is ", $array[$i], " "; $i++; } ? > </body> </html> 11

Looping: while 12

Looping: for and foreach <html><head></head> <body> <? php $array = array(5, 4, 3, 2, for ($i=0; $i<5; $i++) { echo "Element $i is } $i=0; foreach ($array as $value) echo "Element $i is $i++; } ? > foreach is good for </body> </html> 1); ", $array[$i], " "; { ", $value, " "; associative array 13

Looping: for and foreach 14

User Defined Functions <html><body> <? php $array = array(5, 4, 3, 2, 1); function quicksort($myarray) { $len = count($myarray); if ($len <= 1) return $myarray; $pivot = $myarray[$len-1]; unset($myarray[$len-1]); foreach ($myarray as $value) { if ($value < $pivot) $less[] = $value; // append $value to less elseif ($value > $pivot) $more[] = $value; // append $value to more else $equal[] = $value; // append $value to equal } $result = quicksort($less); $result[] = $pivot; if (count($equal) > 0) $result = array_merge($result, $equal); $result = array_merge($result, quicksort($more)); return $result; } echo 'Before sorting: '; print_r($array); echo '<BR>'; $array = quicksort($array); echo 'After sorting: '; print_r($array); ? > </body></html> 15

User Defined Functions 16

Server Variables The $_SERVER is a variable that contains system information <html><head></head> <body> <? php echo "User's IP address: ". $_SERVER["REMOTE_ADDR"]. " "; echo "Referer: ". $_SERVER["HTTP_REFERER"]. " "; echo "Browser: ". $_SERVER["HTTP_USER_AGENT"]; ? > </body> </html> 17

Server Variables l output: User's IP address: 123. 4. 56. 111 Referer: Browser: Mozilla/5. 0 (Windows; U; Windows NT 5. 1; en-US; rv: 1. 8. 1. 12) Gecko/20080201 Firefox/2. 0. 0. 12 18

Form Handling <html> <body> <form action="welcome. php" method="POST"> <p>Enter your name: <input type="text" name="name" /> <p>Enter your age: <input type="text" name="age" /> <p><input type="submit" /> <input type="reset" /> </form> </body> </html> USER clicks the SUBMIT: FORM data goes to welcome. php. POST: Collect values from the FORM, GET: Collect values but make it public. Get is not good for passing PW, for example <html> <body> welcome. php Welcome <? php echo $_POST["name"]. ". "; ? > You are <? php echo $_POST["age"]; ? > years old! </body> </html> 19

Form Handling 20

PHP with My. SQL l Connect to the database server and login $conn = mysqli_connect("host", "username", "password"); l Choose the database mysqli_select_db($conn, "database"); l Submit SQL queries to insert/delete/update data mysqli_query( $conn, "query"); l Close the connection to the database server mysqli_close($conn); 21

PHP with My. SQL l $conn = mysqli_connect("host", "user", "pwd“, “dbname"); l $result=mysqli_query($conn, "SQL query"); $num=mysqli_num_rows($result); $columns=$result->fetch_fields(); $row=mysqli_fetch_row($result); // returns an array $row=mysqli_fetch_assoc($result); // returns an associative array mysqli_close($conn); l l 22

Guestbook Example <html><head><title>My Guestbook</title></head> <body><h 1>Welcome to my Guestbook</h 1> <h 2>Enter your comments below: </h 2> <form action="<? echo "$PHP_SELF"? >" method="POST"> <textarea cols=40 rows=5 name="note" wrap=virtual></textarea> <p><input type="submit" value=" Submit "> </form> <? $link = mysqli_connect('mysql-user', 'username', 'password', 'dbname'); $note = $_POST["note"]; if(isset($note)) { $ts = date("Y-m-d H: i: s"); $query = sprintf("insert into comments values('%s', '%s')", mysqli_real_escape_string($link, $note), $ts); $result = mysqli_query($link, $query); } mysqli_close($link); ? > <h 2>The entries so far: </h 2> <? $link = mysqli_connect('mysql-user', 'username', 'password', 'dbname'); $result = mysqli_query($link, "select * from comments"); while($row = mysqli_fetch_row($result)) { echo $row[1]. " Message: ". $row[0]. " n"; } mysqli_close($link); ? > </body></html> 24

Guestbook Example 25

ANY SQL Injection Attacks!

What is a SQL Injection Attack? l Wikipedia: A code injection technique that exploits a security vulnerability occurring in the database layer of an application. l There are many web applications that take user input from a form – A SQL injection attack involves placing SQL statements in the user input to compromise the database system “All input is evil until proven otherwise. ” Howard & Le. Blanc 27

Example Incorrect password; so no rows will be returned 28

Example Oops! all rows will be returned because ‘x’ =‘x’ is always true SQL injection attacks try to exploit this type of vulnerability 29

Authorization Bypass l l l Simplest SQL injection technique is bypassing login forms Example: Let’s say a user fills out the logon form like this: Login: ' OR ' ' = ' Password: ' OR ' ' = ' PHP File: $user = $_POST[“login”]; $password = $_POST[“password”] SELECT Username FROM Users WHERE Username = '$user' AND Password = '$password'; BECOMES SELECT Username FROM Users WHERE Username = ' ' OR ' ' = ' ' AND Password = ' ' OR ' ' = ' ' i. e, Username equals empty OR empty=empty AND Password=empty OR empty=empty 30

Another Example $SQLQuery = “SELECT * FROM users WHERE username = ‘” + user + “' AND password = ‘” + password + “'; ” http: //www. target. com/login. asp? user=admin&password='%20 OR%20‘x'=‘x $SQLquery becomes “SELECT * FROM users WHERE username = 'admin' AND password = '' OR ‘x' = ‘x'; ” Not a safe query for a Web-based application! You should restrict admins to have access only from a certain machine 31

Another Example l Suppose your code has the following SQL statement $query = “SELECT patient_info FROM patients WHERE SSN = ‘”. $_POST[‘user_input_patient_SSN’]. “’”; Patient SSN: l 999‘ OR ‘x’ = ‘x This creates the following SQL: SELECT patient_info FROM patients WHERE SSN = ‘ 999‘ OR ‘x’ = ‘x’ l Attacker has now successfully caused the entire database to be returned. 32

A More Malicious Example l What if the attacker had instead entered: Patient SSN: 999‘; DROP TABLE patients; - - l Results in the following SQL: SELECT patient_info FROM patients WHERE SSN = ‘ 999’; DROP TABLE patients; --’ – Note how comment (--) to deal with the final quote l This will cause the entire database to be deleted – Depends on knowledge of table name, which is sometimes exposed in debug code called during a database error – Use non-obvious table names, and never expose them to user – Usually data destruction is not your worst fear, as there is low economic motivation 33

Other injection possibilities l Using SQL injections, attackers can: – Add new data to the database u Could be embarrassing to find yourself selling politically incorrect items on an e. Commerce site u Perform an INSERT in the injected SQL – Modify data currently in the database u Could be very costly to have an expensive item suddenly be deeply ‘discounted’ u Perform an UPDATE in the injected SQL – Often can gain access to other user’s system capabilities by obtaining their password 34

Example 35

Example 36

XKCD 37

Defenses l Use provided functions for escaping strings – Many attacks can be thwarted by simply using the SQL string escaping mechanism u' u ' " " – Example: mysqli_real_escape_string($conn, $string) or addslashes ($string) l Not a silver bullet! – Consider: u SELECT fields FROM table WHERE id = 23 OR 1=1 u No quotes here! 38

More Defenses l Check syntax of input for validity l Have length limits on input – Many SQL injection attacks depend on entering long strings l Scan query string for undesirable word combinations that indicate SQL statements – INSERT, DROP, etc. l Limit database permissions and segregate users l Configure database error reporting – Default error reporting often gives away information that is valuable for attackers (table name, field name, etc. ) – Configure so that this information is never exposed to a user 39