Posted on 2018/03/28 at 8:36 pm
Preventing SQL injections is pretty easy once you know what you are doing. Some of you who are new to databases might think about character substitutions, character escaping, or just outright banning of certain characters; but, those options are laborious and not nearly as simple or elegant. Instead, we can use a technique called SQL Parameterization. It's a fancy word for a straightforward process. For this guide, I'll be referencing a database titled Movies.
To explain it simply, what we are going to do is use a "?" in place of inserting our value. We then have our value tied to the "?" which will then be interpreted as a string literal. Since it is being handled as a string literal and is being kept from interpretation, this keeps us from having our databases deleted, stolen, or used in unforeseen ways.The setup looks like this:
// Instead of this. $updateComm = "SELECT title FROM Movies WHERE id = '" . $ID . "'"; // We have this. $updateComm = "SELECT title FROM Movies WHERE id = ?";
Again, the question mark is like a reference marker/bind point. Our data gets tied to it and is never interpreted. We next do a prepare statement that'll allow us to bind values. The first way we can do this is by binding the values to the field individually.Let's take a look:
// We first setup a command with the properly inserted question marks. $updateComm = "UPDATE Movies SET title = ?, link = ?, date = ? WHERE id = ?"; // We then set it up for preparation. $updateStatement = $db->prepare( $updateComm ); // We then insert to the bind points. // Note: They are in order of appearance in the command. // This is why we bind to title first and then links, etc. // Keep this in mind when setting up commands. // Also, we don't start at zero but one. $updateStatement->bindValue( 1, "I, Robot", PDO::PARAM_STR ); $updateStatement->bindValue( 2, "LINK", PDO::PARAM_STR ); $updateStatement->bindValue( 3, "July 7, 2004", PDO::PARAM_STR ); $updateStatement->bindValue( 4, 666999, PDO::PARAM_INT ); // We then execute the command which is properly set up. $updateStatement->execute();
The second option is to just pass the arguments as an array without individual binding. Note once more that it is all based on order of the question marks.
$updateComm = "UPDATE Movies SET title = ?, link = ?, date = ? WHERE id = ?"; // We then set it up for preparation. $updateStatement = $db->prepare( $updateComm ); // Then, just pass parameters as an array to the execute method. $updateStatement->execute( array( "I, Robot", "LINK", "July 7, 2004", 666999) );
In both cases, everything is properly sanitized for you and you don't have to worry about rogue single quotes or DROP TABLE commands getting through. You're done! Easy right? At this point, all you have to do is look up your language and how to do the above steps and implement them for secure database interactions. Look at Rosetta Code for how other languages setup parameterization. In addition, if you've read any of my other articles, pick a project like my PHP7, SQLite3, and Ajax Tutorial to practice this on in addition to learning other skills and techniques.