Switching to Prepared Statements

I have been in the same situation. I was using concatenated statements too, then I switched my application to prepared statements.

the bad news is you are going to change every SQL statement built by concatenating client data to the SQL statement, which almost will be every SQL statement you have in your 50 source files.

the good news is the gain from switching to prepared statements is priceless, for example:

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

the php manual says

If an application exclusively uses
prepared statements, the developer can be sure that no SQL injection
will occur (however, if other portions of the query are being built up
with unescaped input, SQL injection is still possible).

For me, that reason -peace of mind- is enough to pay the cost of changing my source code. , now your clients can type in a form name field robert; DROP table students; -- ;) and you feel safe that nothing is gonna happen

2- you don't need to escape the client parameters anymore. you can directly use them in the SQL statement, something like :

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

instead of

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

which is something you had to do before using prepared statements, which was putting you in danger of forgetting to escape one parameter as a normal human being. and all it takes for an attacker to corrupt your system is just 1 unescaped parameter.

Changing The Code

typically changing the source files is always risky and has pain, especially if your software design is bad and if you don't have an obvious testing plan. but I will tell you what I did to make it as easier as possible.

I made a function that every database interaction code is going to use, so you can change what you want later in one place -that function- you can make 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;
    }
}

Now, you can use this interface anywhere you want in your source files, for example let's change your current SQL statements you have provided in the question. Let us change this

$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;
    }
}

Into this

$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 am not entering any data to the DB, is it still vulnerable to injection?

Yes, Sql Injection attack is applied by concatenating bad string to your SQL statement. whither it is an INSERT, SELECT, DELETE, UPDATE. for example

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

something like that could be exploited by

// 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 with switching your software to prepared statements, and remember that the peace of mind you are going to get from knowing that whatever happens, you are safe from SQL injection attacks is worth the cost of changing the source files

Change statement with prepared statement in Java

Your current code is unsafe because you are concatenating values into a query string. This makes your code vulnerable to SQL injection. To address that, you need to switch to a prepared statement, using parameter placeholders in your statement text, and then set the value on the statement before execution.

An example of using prepared statement would be (some columns elided for brevity):

try (PreparedStatement pstmt = cnx.prepareStatement(
        "INSERT INTO `user`(`nom`, `email`, ...) values (?, ?, ...)")) {
    pstmt.setString(1, e.getNom());
    pstmt.setString(2, e.getEmail());
    // ...

    pstmt.executeUpdate();
}

change from Statements to PreparedStatements

// Create the connection (unchanged)
Properties connInfo = new Properties();
connInfo.put("user", "Main");
connInfo.put("password", "poiuyt");
Connection connection  = DriverManager.getConnection("jdbc:mysql://localhost/ABCNews", connInfo);

// Prepare the statement - should only be done once, even if you are looping.
String sql = "insert into abcnews_topics VALUES (null, ?)";
PrepatedStatement stmt = connection.prepareStatement(sql);

// Bind varaibles
stmt.setString (1, text_topic); // Note that indexes are 1-based.

// Execute
stmt.executeUpdate();

Convert normal SQL statements to prepared statements

The problem is that you appear to pass the whole query as a string to your dbquery() method.

Prepared statements only protect against sql injection in case a value is passed as a parameter to the query, not if the value is already in the query.

Since your entire query is a single string, with no placeholders and no values passed separately, you cannot use the prepared statements to protect against sql injection without changing how you pass the queries and parameters to dbquery() method. Therefore, you need to rewrite your queries in case you want to take advantage of sql prevention feature of the prepared statements.

Converting regular mysql into prepared statements

You don't need to change a query to a prepared statement if it has no PHP variables in it. If it has just constant expressions, it's safe from SQL injection.

$sql = "SELECT * from users where userid=10"; // Safe!
$stmt = $pdo->query($sql);
$data = $stmt->fetchAll();

You don't need to change a query that contains PHP variables, as long as the value of that variable is a constant specified in your code. If it doesn't take its value from any external source, it's safe.

$uid = 10;
$sql = "SELECT * from users where userid=$uid"; // Safe!
$stmt = $pdo->query($sql);
$data = $stmt->fetchAll();

You don't need to change a query that contains PHP variables, as long as you can filter the value to guarantee that it won't risk an SQL injection. A quick and easy way to do this is to cast it to an integer (if it's supposed to be an integer).

$uid = (int) $_GET['uid'];
$sql = "SELECT * from users where userid=$uid"; // Safe!
$stmt = $pdo->query($sql);
$data = $stmt->fetchAll();

That leaves cases where you are using "untrusted" values, which may have originated from user input, or reading a file, or even reading from the database. In those cases, parameters are the most reliable way to protect yourself. It's pretty easy:

$sql = "SELECT * from users where userid=?"; // Safe!

// two lines instead of the one line query()
$stmt = $pdo->prepare($sql);
$stmt->execute([$_GET['uid']]);

$data = $stmt->fetchAll();

In a subset of cases, you need one additional line of code than you would normally use.

So quit your whining! ;-)

Re your comment about doing prepared statements in mysqli.

The way they bind variables is harder to use than PDO. I don't like the examples given in http://php.net/manual/en/mysqli.prepare.php

Here's an easier way with mysqli:

$sql = "SELECT * from users where userid=?"; // Safe!

$stmt = $mysqli->prepare($sql);
$stmt->bind_param('i', $_GET['uid']);
$stmt->execute();
$result = $stmt->get_result();

$data = $result->fetch_all();

I don't like the stuff they do in their examples with bind_result(), that's confusing and unnecessary. Just use get_result(). So with mysqli, you need two more lines of code than you would with PDO.

I've written query wrappers for mysqli that emulate the convenience of PDO's execute() function. It's a PITA to get an array mapped to the variable-arguments style of bind_param().

See the solution in my answers to https://stackoverflow.com/a/15933696/20860 or https://stackoverflow.com/a/7383439/20860

Switching from simple queries to prepared statements

While I still haven't found WHY my original code for prepared statements ended up crashing the DB few hours later, I did eventually find a SIMPLE and WORKING out-of-the-box alternative by using a popular class made especially for this purpose: https://github.com/colshrapnel/safemysql

This class is pretty straightforward so even a newbie like myself was able to implement it using the examples found on their Github page.

Special thanks goes out to @YourCommonSense for pointing me in the right direction.

When should I use prepared statements?

tl/dr

Always. 100% of the time, use it. Always; and even if you don't need to use it. USE IT STILL.

mysql_* functions are deprecated. (^{Notice the big red box?})

Warning This extension was deprecated in PHP 5.5.0, and it was removed
in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be
used. See also MySQL: choosing an API guide and related FAQ for more
information. Alternatives to this function include:

mysqli_connect()

PDO::__construct()

You'd be better off using PDO or MySQLi. Either of those 2 will suffice as compatible libraries when using prepared statements.

Trusting user input without prepared statements/sanitizing it is like leaving your car in a bad neighborhood, unlocked and with the keys in the ignition. You're basically saying, just come on in and take my goodies Sample Image

You should never, and I mean never, trust user input. Unless you want this:

SQL Injection

In reference to the data and storing it, as stated in the comments, you can never and should never trust any user related input. Unless you are 101% sure the data being used to manipulate said databases/values is hard-coded into your app, you must use prepared statements.

Now onto why you should use prepared statements. It's simple. To prevent SQL Injection, but in the most straight forward way possible. The way prepared statements work is simple, it sends the query and the data together, but seperate (if that makes sense haha) - What I mean is this:

Prepared Statements
Query: SELECT foo FROM bar WHERE foo = ?
Data:  [? = 'a value here']

Compared to its predecessor, where you truncated a query with the data, sending it as a whole - in turn, meaning it was executed as a single transaction - causing SQL Injection vulnerabilities.

And here is a pseudo PHP PDO example to show you the simplicity of prepared statements/binds.

$dbh = PDO(....); // dsn in there mmm yeahh
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

^{Taken from PHP Manual for PDO Prepared Statements}

More Reading

How can I prevent SQL-injection in php?
What is SQL-injection? (^{Simple Terms})

Should we always use prepared statements in MySQL and php or when to use these?

Non-prepared statements are sufficient if you have an SQL query that is entirely hard-coded, and needs no PHP variables in the SQL.

Here's an example:

$result = $mysqli->query("SELECT * FROM mytable WHERE updated_at > NOW() - INTERVAL 7 DAY");

The query is self-contained. It's just a fixed string, entirely under control of your application. There's no way any untrusted content can affect the query.

If your query needs some variable part, then use query parameters, like this:

$stmt = $mysqli->prepare("SELECT * FROM mytable WHERE updated_at > NOW() - INTERVAL ? DAY");
$stmt->bind_param("i", $number_of_days);
$stmt->execute();

The point of query parameters is to separate potentially untrusted content from the SQL parsing step. By using parameters, the value of the bound variable is not combined with the query until after the SQL has been parsed. Therefore there is no way the bound parameter can affect the logic of the query — the parameter will be limited to act as a single scalar value in the query.

Switching to Prepared Statements