SQL with PHP and SQL Injection Attacks

SQL with PHP and • SQL Injection Attacks

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>

Basic PHP Syntax output:

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! <br> "; $mystr='1234'; echo "String is $mystr <br>"; $a = 1234; echo "Integer is $a <br />"; $a = 314.2E-2; echo "Float is $a <br />"; echo 'Arnold said: "I\'ll be back"', "<br /> "; $str = <<<EOD An example of a string that spans Multiple lines EOD; echo $str; ?> </p> </body></html> <p><br> line <br/><br> line2<br/><p/> br:line brak, <p> paragraph. <<<EOD EOD like “” with multiple lines. ‘‘ for simple strings and “” for strings with variables, etc.

Scalars

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

Arrays

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

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

Conditionals: if else

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], "<br>"; $i++; } ?> </body> </html>

Looping: while

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

Looping: for and foreach

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>

User Defined Functions

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

Server Variables 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

Form Handling <html> <body> <form action="welcome.php" method="POST"> <p>Enter your name: <input type="text" name="name" /> <br> <p>Enter your age: <input type="text" name="age" /> <br> <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 echo $_POST["name"]. ". "; ?><br> You are <?php echo $_POST["age"]; ?> years old! </body> </html> welcome.php

Form Handling

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

PHP with MySQL $conn = mysqli_connect("host","user","pwd“, “dbname"); $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);

Guestbook Example <html><head><title>My Guestbook</title></head> <body><h1>Welcome to my Guestbook</h1> <h2>Enter your comments below:</h2> <form action="<? echo "$PHP_SELF"?>" method="POST"> <textarea cols=40 rows=5 name="note" wrap=virtual></textarea> <p><input type="submit" value=" Submit "> </form> <? //PHP code goes here ?> <h2>The entries so far:</h2> <? //More PHP code goes here ?> </body></html>

Guestbook Example <html><head><title>My Guestbook</title></head> <body><h1>Welcome to my Guestbook</h1> <h2>Enter your comments below:</h2> <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); ?> <h2>The entries so far:</h2> <? $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] . "<br>\n"; } mysqli_close($link); ?> </body></html>

Guestbook Example

ANY SQL Injection Attacks!

What is a SQL Injection Attack? • Wikipedia: A code injection technique that exploits a security vulnerability occurring in the database layer of an application. • 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 & LeBlanc

Example Incorrect password; so no rows will be returned

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

Authorization Bypass • 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 UsersWHERE 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

Another Example $SQLQuery = “SELECT * FROM users WHERE username = ‘” + user + “' AND password = ‘” + password + “';” http://www.target.com/login.asp?user=admin&password='%20OR%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

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

A More Malicious Example What if the attacker had instead entered:Patient SSN: 999‘; DROP TABLE patients; - - 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 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

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

Example

XKCD

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

More Defenses Check syntax of input for validity Have length limits on input Many SQL injection attacks depend on entering long strings Scan query string for undesirable word combinations that indicate SQL statements INSERT, DROP, etc. Limit database permissions and segregate users 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

SQL with PHP and SQL Injection Attacks