Go to prepared orders

I was in the same situation. I also used concatenated statements, then I switched my application to prepared statements.

bad news . You are going to modify every SQL statement created by concatenating customer data into an SQL statement, which will almost be every SQL statement you have in your 50 source files.

the good news is the gains from going to prepared statements are priceless, for example:

1 - you will never worry about something called "SQL Injection attack"

php manual says

If the application exclusively uses prepared statements, the developer can be confident that SQL injection will occur (however, if other parts of the query are created with unrelated input, SQL injection is still possible).

For me this reason is mind-set - enough to pay the cost of changing my source code ... now your clients can enter the form name field robert; DROP table students; -- ;)

and you feel safe nothing will happen

2- you don't need to go out of client options anymore. you can use them directly in a SQL statement, for example:

$query = "SELECT FROM user WHERE id = ?";
$vars[] = $_POST['id'];

      

        
        
        
      

    

instead

$id = $mysqli->real_escape_string($_POST['id']);
$query = "SELECT FROM user WHERE id = $id";

      

        
        
        
      

    

what you needed to do before using prepared statements, which prevented you from forgetting to avoid one parameter as an ordinary person. and all it takes for an attacker to mess up your system is just 1 unlearned parameter.

Code change

usually changing the source files is always dangerous and painful, especially if your software design is poor and if you don't have an obvious test plan. but I will tell you what I did to make it as easy as possible.

I made a function that every database interaction code will use, so you can change what you want later in one place - this function - you can do something like this

class SystemModel
{
    /**
     * @param string $query
     * @param string $types
     * @param array $vars
     * @param \mysqli $conn
     * @return boolean|$stmt
     */
    public function preparedQuery($query,$types, array $vars, $conn)
    {
        if (count($vars) > 0) {
            $hasVars = true;
        }
        array_unshift($vars, $types);
        $stmt = $conn->prepare($query);
        if (! $stmt) {
            return false;
        }
        if (isset($hasVars)) {
            if (! call_user_func_array(array( $stmt, 'bind_param'), $this->refValues($vars))) {
                return false;
            }
        }
        $stmt->execute();
        return $stmt;
    }

    /* used only inside preparedQuery */
    /* code taken from: https://stackoverflow.com/a/13572647/5407848 */
    protected function refValues($arr)
    {
        if (strnatcmp(phpversion(), '5.3') >= 0) {
            $refs = array();
            foreach ($arr as $key => $value)
                $refs[$key] = &$arr[$key];
                return $refs;
        }
        return $arr;
    }
}

      

        
        
        
      

    

You can now use this interface anywhere in your source files, for example, you can change the current SQL queries you have given in the question. Let's change it

$mysqli = new mysqli('localhost', "root", "", "testdb");
$addresult = "
                SELECT a.firstnames, a.surname, a.schoolrole, a.datejoined 
                FROM teachers a LEFT JOIN schools b ON a.schoolid = b.id 
                WHERE b.id = '".$inputvalues['schoolid']."'";

if( $result = $mysqli->query($addresult) ) {
    while($row = $result->fetch_all())
    {
        $returnResult = $row;
    }
}

      

        
        
        
      

    

In that

$mysqli = new mysqli('localhost', "root", "", "testdb");
$sysModel = new SystemModel();
$addresult = "
                SELECT a.firstnames, a.surname, a.schoolrole, a.datejoined
                FROM teachers a LEFT JOIN schools b ON a.schoolid = b.id
                WHERE b.id = ?";
$types = "i"; // for more information on paramters types, please check :
//https://php.net/manual/en/mysqli-stmt.bind-param.php
$vars = [];
$vars[] = $inputvalues['schoolid'];

$stmt = $sysModel->preparedQuery($addresult, $types, $vars, $mysqli);
if (!$stmt || $stmt->errno) {
   die('error'); // TODO: change later for a better illustrative output
}
$result = $stmt->get_result();
$returnResult = [];
while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
    $returnResult[] = $row;
}

      

        
        
        
      

    

Also, if I don't enter any data in the DB, is it still vulnerable to injection?

Yes, Sql Injection attack is applied by concatenating a dense string into your SQL statement. where INSERT

, SELECT

, DELETE

, UPDATE

. eg

$query = "SELECT * FROM user WHERE name = '{$_GET['name']}' AND password = '{$_GET['pass']}'"

      

        
        
        
      

    

something like this could be used

// exmaple.com?name=me&pass=1' OR 1=1; -- 

      

        
        
        
      

    

which will result in a SQL statement

$query = "SELECT * FROM user WHERE name = 'me' AND password = '1' OR 1=1; -- '"
//executing the SQL statement and getting the result
if($result->num_rows){
    //user is authentic
}else{
    //wrong password
}
// that SQL will always get results from the table which will be considered a correct password

      

        
        
        
      

    

Good luck switching your software to prepared statements and remember that the peace of mind you get knowing that no matter what happens, you can be safe from SQL injection attacks, well worth the cost of modifying the source files