16 Dec 2022
Secure Coding SQLi’s the king, the carolers singSQL is traditional language used to query DBs. Any app that relies on a DB needs to create SQL sentences on the fly to retrieve information. SQL syntax supposed to resemble straightforward English sentences.
SELECT * FROM toys;
Retrieve all columns from table “toys”
SELECT name, quantity From toys;
Retrieve “name” and “quantity” columns from table “toys”
All rows returned for columns in previous examples.
SELECT name, quantity FROM toys WHERE quntity >= 20;
Retrieve rows in columns “name” and “quantity” with quantities of 20 or more
First connect to DB. PHP includes mysqli_connect() function. Receives IP or name of DB server ($server), then username ($user), password ($pwd), finally schema to use ($schema). Function returns connection handler that acts as variable that holds connection information to the DB to send further SQL queries.
$server="db";
$user="logistics_user";
$pwd="somePass123";
$schema="logistics";
$db=mysqli_connect($server,$user,$pwd,$schema);
Once connection is made, issuse SQL queries using mysqli_query() function. First parameter passed to this function is connection handler, second parameter is the query string.
$query="select * from users where id=1";
$elves_rs=mysqli_query($db,$query);
Executing query returns SQL result set stored in the $elves_rs variable. Sample web address usage: http://svr.server.tld/webapp/elf.php?id=2
provides query:
$query="select * from users where id=".$_GET['id'];
$elves_rs=mysqli_query($db,$query);
Coding this way introduces a SQLi vuln that can allow dumping entire DB. Code shouldn’t take untrusted code from user and add directly to SQL query with no questions asked.
More details on SQL injection here: https://tryhackme.com/room/sqlinjectionlm
One of the easiest and most effective ways to prevent SQL injections is to make sure user manipulated data is the type expected. If input data should be an integer, can use intval() to pull only integers from strings and if none return 0.
Separate the syntax of the SQL sentence from the parameters used and allow the DB to put pieces together instead of depending on PHP or the programmer. Instead of:
$query="select * from toys where name like '%".$_GET['q']."%' or description like '%".$_GET['q']."%'";
$toys_rs=mysqli_query($db,$query);
use
$query="select * from toys where name like ? or description like ?";
$stmt=mysqli_prepare($db, $query);
? tells DB to run a query that takes two parameters as inputs. Query gets passed to mysqli_prepare() function, which prepares a statement with given syntax. Executing requires MySQL knowing what value to put to each placeholder. Can attach using mysqli_stmt_bind_param() function. Requires two parameters, first is reference to the prepared statement. Second is a string composed of one letter per placeholder to be bound. Letters indicate each variables type (i.e. s for string, i for int, d for float). Then pass the variables themselves, adding as many variables as placeholders defined with ? in query.
$q = "%".$_GET['q']."%";
mysqli_stmt_bind_param($stmt, 'ss', $q, $q);
Once statement is created and parameters bound, then use mysqli_stmt_execute() to execute. It’s only parameter is $stmt. Once executed, retrieve result set with mysqli_stmt_get_result() with $stmt> as only parameter.
Find the flags while fixing the vulns. Two flags with code from task, two flags from own devices.