how can i protect code from SQL injection.

[jameshumphries47]

what is the best way to secure your site from people ruining it with sql injection? 

[SansVarnic]

Could you fix your title? I thought you were posting a way to do it. I would like to know answer as well.

@jameshumpries47

[msevilgenius]

Use Prepared statements

[jameshumphries47]

Could you fix your title? I thought you were posting a way to do it. I would like to know answer as well.

@jameshumpries47

done im thinking about making a hardware comparison site, or an online to do list site where you can compare with people in your local area

[SNIT]

You can create a controller to validate an entry (i.e string from a search box) before submitting the query. This is called the Model, View, Controller Architecture (MVC), the model is the database, the View is the front-end and the controller is go-between, it also works well with prepared statements.

 

Alternatively you could format the string/value in-line.

[PrimeSonic]

Here's a video on the subject: 

 

If your website has any layer that manages access to the database, something like Entity Framework. Then you don't need to worry at all.

 

If your backend code has to talk to the database directly, then you have two options and one of them should be mandatory.

 

Use Prepared statements

They're also sometimes called "stored procedures" depending on where you look.

This is a must. Avoid ever just sending a plain SQL statement to your database from your backend code if you can ever help it.

 

Second, would be trying to sanitize anything that would go to the database, where you try to filter out potentially dangerous characters.

If you run everything through prepared statements, then you don't really need to worry about doing this too much.

But, you can at least try to help filter out and validate input wherever it's needed.

 

Still, just use prepared statements in the SQL engine of your choice and you'll be fine. The rest is just making sure your website behaves itself and doesn't allow anyone to even try to enter weird stuff in places it shouldn't be allowed.

[jas57]

Try using prepared statements.

[asquirrel]

#Returns the $_GET value as an array if the variable is set. False otherwise.#[0] = PHP/HTML escaped#[1] = mysql escapedfunction get_GETValue($mysql,$name){    $tmp=array("","");    if( isset($_GET["$name"]) )    {        $tmp[0] = htmlspecialchars($_GET["$name"]);        $tmp[1] = mysqli_real_escape_string($mysql,$_GET["$name"]);        if( $tmp[0] === "" && $tmp[1] === "" ) { return false; }        return $tmp;    }    return false;}#Returns the $_POST value as an array if the variable is set. False otherwise.#[0] = PHP/HTML escaped#[1] = mysql escapedfunction get_POSTValue($mysql,$name){    $tmp=array("","");    if( isset($_POST["$name"]) )    {        $tmp[0] = htmlspecialchars($_POST["$name"]);        $tmp[1] = mysqli_real_escape_string($mysql,$_POST["$name"]);        if( $tmp[0] === "" && $tmp[1] === "" ) { return false; }        return $tmp;    }    return false;}

This implementation is useful to me as I use input sent via GET or POST (depending on the page) to either be used in PHP statements or SQL statements, thus, I just create my own array of sanitized inputs. mysqli_real_escape_string() will deal with sanitizing input for SQL use (if you aren't using mysql, google!). htmlspecialchars will sanitize input for PHP data.

 

There are a few people who will see me post this code and probably lay into me on how I need to be using PDO and prepared statements. I have these people on ignore because they are useless individuals. PDO is great if you enjoy programming in Java or other Object Oriented languages, but I am a C programmer, and functional PHP can do everything PDO can with no added security weaknesses.

 

In PHP prepared statements are also a useful security feature. The variables you pass via a prepared statement are NOT forwarded to the SQL syntax parser and the SQl engine knows to only expect data in those fields, so any SQL injected through them *should* lead to a syntax error. The reason I have a lot of 'it should ...' in this is because PHP's native SQL libraries are well designed and behave properly. There are SQL libraries which simply wrap the prepared parameters into a finished SQL statement and pass the entire thing along to the SQL syntax parser. In this case you get 0 security benefit from using a prepared statement. This is, effectively, a design flaw/bug with the library in question, but you do need to be on the lookout for it if you start using other people's libraries in your code.