Preventing Sql injections on Affiliates/Trade Link pages.

[Noximity]

I recently created a script that allows users to get free items using another persons code. Soon I had figured out that users were typing "TESTCODE', admin='1" , "TESTCODE', balance='1" And a bunch of other stuff on this page to exploit their balances and other things. I've bee trying to clean up the code and prevent the users from exploiting this page but I am bamboozled on trying to fix it. I'm not too sure how to go about fixing it, If anyone knows what to change/type to fix this problem it is appreciated, Thanks! Help is highly appreciated

 

 

Affiliates Page Code:

<?php require_once 'templates/_header.php'; ?><input type="hidden" id="steamid" value="<?php echo $_SESSION['steamid']; ?>">


<?php
$totalBet = 0;
$totalReferredUsers = 0;
$totalEarnings = 0;
$availableEarnings = 0;
if(!isset($_SESSION['steamid']))
{
?>
<div class="notice notice-danger">
<strong>
Error.
</strong> 
<span style="display: block">
You don't have access to that site.
</span>
</div>
<?php
}
else
{
?>


<?php


$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) 
{
die("Connection failed: " . $conn->connect_error);
}


// count referred users
$sql = "SELECT played FROM users WHERE referredBy='".$_SESSION['steamid']."'";
$result = $conn->query($sql);


if ($result->num_rows > 0) 
{
while($row = $result->fetch_assoc()) 
{
$totalBet += $row['played'];
}
} 


$totalReferredUsers = mysqli_num_rows($result);


// get user data
$sql = "SELECT refEarningsTotal, refEarningsAvailable, code FROM users WHERE steamid='".$_SESSION['steamid']."'";
$result = $conn->query($sql);


if ($result->num_rows > 0) 
{
while($row = $result->fetch_assoc()) 
{
$totalEarnings = $row['refEarningsTotal'];
$availableEarnings = $row['refEarningsAvailable'];
$code = $row['code'];
}
} 


?>


<div class="row">
<h2><i class="fa fa-server"></i> Afilliates</h2>
<div class="col-md-12">
<div id="afMsg"></div>


<div class="input-group" style="margin-bottom:20px">
<input type="text" id="code333" class="form-control" value="<?php echo $code; ?>" placeholder="Your referral code...">
<span class="input-group-btn">
<a class="btn btn-primary" role="button" onclick="updateCode();">Update</a>
</span>
</div>


<table class="table table-bordered">
<tbody>
<tr>
<th>Total referred users</th>
<td><?php echo $totalReferredUsers; ?></td>
</tr>
<tr>
<th>Total bet</th>
<td><?php echo $totalBet; ?> $</td>
</tr>
<tr>
<th>Total earnings</th>
<td><?php echo $totalEarnings; ?> $</td>
</tr>
<tr>
<th>Available earnings</th>
<td><?php echo $availableEarnings; ?> $</td>
</tr>
</tbody>
</table>


<a role="button" class="btn btn-success btn-block" onclick="collectEarnings();">Collect earnings</a>


<table class="table table-bordered" style="margin-top:25px;"> 
<thead> 
<tr> 
<th>Steam ID</th> 
<th>Total bet</th> 
</tr> 
</thead> 
<tbody> 
<?php
$sql2 = "SELECT steamid, played FROM users WHERE referredBy='".$_SESSION['steamid']."'";
$result2 = $conn->query($sql2);


if ($result2->num_rows > 0) 
{
while($row2 = $result2->fetch_assoc()) 
{
echo '<tr><td>'.$row2['steamid'].'</td>';
echo '<td>'.$row2['played'].'</td>';
} 
}


$conn->close();
?>
</tbody> 
</table>
</div>           
</div>




<?php
}
?>


<?php require_once 'templates/_footer.php'; ?>

If the user already has a referral code and the user updates it this script is executed:

<?php


include 'settings.php';


$code = strtoupper($_POST['code']);


$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) 
{
die("Connection failed: " . $conn->connect_error);
}


$code = $_POST['code'];
$sql = "SELECT id FROM users WHERE code='".$code."'";
$result = $conn->query($sql);


if ($result->num_rows > 0) 
{
?>


<div class="notice notice-danger">
<strong>
Error.
</strong> 
<span style="display: block">
This code is already in use.
</span>
</div>


<?php
exit;
} 


if(empty($code))
{
?>


<div class="notice notice-danger">
<strong>
Error.
</strong> 
<span style="display: block">
Code can't be empty.
</span>
</div>


<?php
}


else
{
// update code
$sql = "UPDATE users SET code='".$code."' WHERE steamid='".$_POST['steamid']."'";
$result = $conn->query($sql);


if ($conn->query($sql) === TRUE) 
{
?>


<div class="notice notice-success">
<strong>
Success!
</strong> 
<span style="display: block">
Your referral code has been changed to <strong><?php echo $code; ?></strong>.
</span>
</div>


<?php
}
else
{
?>


<div class="notice notice-danger">
<strong>
Database error.
</strong> 
<span style="display: block">
Please try again. (5)
</span>
</div>


<?php
}
}


$conn->close();


?>

 

Trade Link update code:

<?php

include 'settings.php';


$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) 
{
die("Connection failed: " . $conn->connect_error);
}


$sql = "UPDATE users SET tlink='".$_POST['link']."&token=".$_POST['token']."' WHERE steamid='".$_POST['steamid']."'";
$result = $conn->query($sql);


if ($conn->query($sql) === TRUE) 
{
?>


<div class="notice notice-success">
<strong>Success!</strong> 
<span style="display:block">Your tradelink has been updated.</span>
</div>


<?php
}
else
{
?>


<div class="notice notice-danger">
<strong>Error!</strong> 
<span style="display:block">There was en error while updating your tradelink. Try refreshing site.</span>
</div>


<?php
}


$conn->close();


?>

[Jacques1]

It's not just the SQL queries. Your entire code is riddled with bugs that can lead to all kinds of syntax errors or injection vulnerabilties. For example, you're making the same mistake with your HTML markup.

 

I strongly recommend you put your code aside and make sure you get a basic understanding of code correctness and security before going on:

• Never insert dynamic data into code contexts (SQL, HTML, shell commands, ...) without careful preparation. If you just drop a PHP variable into an SQL query or HTML document, even a completely harmless string like “O'Donnell” or “x<y” can cause havok, because it screws up the syntax of the surrounding context. And as you've seen, this bug can also be exploited to purposely inject code fragments. You need to either completely separate the data from the code (see prepared statements for SQL queries)  or escape the data so that it won't interfere with the code (see HTML-escaping for HTML contexts).
• Never trust external input without careful validation. Whenever you pull data out of a source like $_GET or $_POST or $_SESSION or your database, it can be badly broken or malicious. So before you make any assumptions about this data, check it. Note that I used the broader term “external input” as opposed to “user input”, because even a source which isn't directly under the user's control (like the session or database) might be manipulated or simply buggy.

Unfortunately, this is easier said than done, and PHP itself doesn't support you in any way. So if you try to implement this on a case-by-case basis, you're likely to screw up. You need a systematic approach with strict coding rules, the right tools and multiple layers of security:

• In general, all SQL queries should be entirely static (i. e. not involve any string concatenation). If you need to include dynamic data, use a prepared statement. Concatenation is only valid when you have to build a complex query from multiple SQL fragements.
• Use an HTML template engine with auto-escaping (like Twig) instead of assembling the HTML markup directly in your script.
• Put your JavaScript code and CSS rules into separate files instead of throwing everything onto one big pile of PHPSQLHTMLJavaScriptCSS. Chaos is a security risk.
• Adopt modern security mechanisms like Content Security Policy.

Edited October 30, 2016 by Jacques1