How to secure SQL query from input?

Question

I am posting parameter to a page in php & then I have to insert it into database but I don't know how to secure the input parameter

<?php
$con=mysqli_connect("example.com","peter","abc123","my_db");
// Check connection
if (mysqli_connect_errno())
{
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

mysqli_query($con,"INSERT INTO Persons (FirstName, LastName, Age)
VALUES ($_POST['FirstName'], $_POST['LastName'],$_POST['Age'])");


mysqli_close($con);
?>

If it involves user input, then use parameterized queries to avoid SQL injection. It's a possible duplicate of [this question](http://stackoverflow.com/questions/60174/). — Amal Murali, Nov 17 '13 at 06:49
take a look at [OWASP recommendations](https://www.owasp.org/index.php/PHP_Security_Cheat_Sheet) — Greg, Nov 17 '13 at 06:50

Brandon Buck · Accepted Answer · 2013-11-17T07:20:02.433

You can easily do this with the mysqli set of functions by escaping those strings beforehand. The MySQL PHP drivers contains a function to safely escape strings for insertion into a string -> mysqli_real_escape_string.

That would change your query to:

mysqli_query($con,"INSERT INTO Persons (FirstName, LastName, Age) VALUES ('" . mysqli_real_escape_string($_POST['FirstName']) . "', '" . mysqli_real_escape_string($_POST['LastName']) . "', '" . mysqli_real_escape_string($_POST['Age']) . "')");

This will handle the majority of your concerns with securing input for SQL.

Optionally

Take full advantage of the driver escaping for other types and safer queries by using prepared statements, which you can also do with mysqli like:

// Prepare our query
$stmt = mysqli_prepare($con, "INSERT INTO Persons (FirstName, LastName, Age) VALUES (?, ?, ?)");

// Bind params to statement
mysqli_stmt_bind_param($stmt, "ssi", $_POST["FirstName"], $_POST["LastName"], $_POST["Age"]);

// Execute the query
mysqli_stmt_execute($stmt);

@user2522767 `mysqli_real_escape_string` is most likely not the culprit, but some other thing you're doing in the code file as well. — Brandon Buck, Nov 17 '13 at 10:49

score 0 · Answer 2 · answered Nov 17 '13 at 06:54

0

You will need to do multiple things. First of all, why are you using the usual MYSQL methods? You should use PDO objects. http://php.net/manual/en/book.pdo.php

Then you can use the prepare() method and then insert it as an array.

Please check http://php.net/manual/en/pdo.prepared-statements.php

answered Nov 17 '13 at 06:54

Hozikimaru

1,144
1
9
20

You can use `prepare` with `mysqli` - Why should he have to switch to PDO and forgo this method? – Brandon Buck Nov 17 '13 at 07:13
PDO is the secure method that you have more control over and more secure. If you want to see more reasons then see http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/ – Hozikimaru Nov 17 '13 at 07:16
I'm not interested in reading a tutorial, if you're recommending to swap to PDO you should explain why - he asked how to do it and most likely wanted to stick with his current setup. Why should he need to research your links? – Brandon Buck Nov 17 '13 at 07:17
PDO is a bit slower than the mysql_* But it has great portability. PDO provides single interface across multiple databases. That means you can use multiple DB without using mysql_query for mysql, mssql_query for MS sql etc. Just use something like $db->query("INSERT INTO...") always. No matter what DB driver you are using. So, for larger or portable project PDO is preferable. Even zend framework use PDO. Good enough for you izuriel? - > http://stackoverflow.com/questions/1402017/php-pdo-vs-normal-mysql-connect – Hozikimaru Nov 17 '13 at 07:19
I didn't ask the question, I just offered advice for you to improve the quality of your answer. Posting links isn't really helpful - despite what you might think. Check the first answer [here](http://meta.stackexchange.com/questions/8231/are-answers-that-just-contain-links-elsewhere-really-good-answers) on MetaStackOverflow for more information. – Brandon Buck Nov 17 '13 at 07:22
izuriel, I definitely understand but as you probably would know, PDO is a very large subject and it would take more than couple of minutes to explain. Also, the best learning method would be to explore it and read the documentations which is what I provided. Thank you for your recommendation. I hope it is now clear why PDO should be used. – Hozikimaru Nov 17 '13 at 07:24
If you can't explain it then are you sure it's a valid answer to "How to secure SQL query from input?" Show an example in PDO, give a brief explanation about why PDO is better than `mysqli` or it's other driver companions - etc. I didn't ask because I know how to do this so my knowledge is irrelevant to your question - the person who _doesn't_ asked and you should try and give him some samples/explanation in your answers. Not just for him, but for everyone else who will find this question in the future and want to learn a little something. Links die, text stays here. – Brandon Buck Nov 17 '13 at 07:27
PHP documentation link would not die. ;) I will follow your advice for the next question for sure. – Hozikimaru Nov 17 '13 at 07:29

score 0 · Answer 3 · answered Nov 17 '13 at 07:09

0

the best thing for you to do is to use a framework like CodeIgniter, Kohana, Zend ... that already has secure classes built in. if you dont want to use the frameworks you can just take these classes.

another way, you can use ORM's. you can use redbean http://www.redbeanphp.com/ the best and easiest ORM i have ever used

answered Nov 17 '13 at 07:09

zion ben yacov

715
6
13

While it may be the best thing, it doesn't necessarily make for a practical answer - I see this as more of a comment. – Brandon Buck Nov 17 '13 at 07:13

How to secure SQL query from input?

3 Answers3

Linked