PHP - Preventing SQL Injection on INSERT STATEMENT

Question

I'll keep this short and simple, I am trying to apply some code to my insert query to prevent SQL injections. See the code below:

$insertquery = mysql_query("INSERT INTO users (firstname, lastname, email, username, password) VALUES ('".mysql_real_escape_string($fname)."', '".mysql_real_escape_string($lname)."', '".mysql_real_escape_string($email)."', '".mysql_real_escape_string($username)."', .'".mysql_real_escape_string($pass)."')");
            header("location:index-login-page.php?msg1=Thank you for choosing SIAA, please login.");

The above code does not insert any data but it still prints the msg1 message. What am I doing wrong or is it even possible to prevent SQL injections on an insert statement.

Thank You

Sohail.

there is a popular question in sof. http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Tintu C Raju, Nov 03 '15 at 11:30
Basic debugging: echo your query before executing it. What is displayed? — Jocelyn, Nov 03 '15 at 14:59

Ninju · Accepted Answer · 2015-11-03T11:55:29.327

3

Use PDO and prepared queries.Use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.Have a look at below example.

($conn is a PDO object)

$conn = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');
$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $conn->prepare("INSERT INTO users VALUES(:firstname, :lastname)");
$stmt->bindValue(':firstname', $firstname);
$stmt->bindValue(':lastname', $lastname);
$stmt->execute();

edited Nov 03 '15 at 11:55

answered Nov 03 '15 at 11:31

Ninju

2,522
2
15
21

Hi Ninju, where you've placed ($conn is a PDO object) - I'm getting an error saying unexpected 'is'.. why is this? – Sohail Arif Nov 03 '15 at 11:52
i have edited my answer.Using PDO to access a MySQL database real prepared statements are not used by default. – Ninju Nov 03 '15 at 11:56
but what if I've written 'include("dbconfig.php")' on top of my php file? – Sohail Arif Nov 03 '15 at 11:59
You need to set the PDO connection in dbconfig.php. – Ninju Nov 03 '15 at 12:04
Ok, I'll try that now. – Sohail Arif Nov 03 '15 at 12:06
I'm still getting a syntax error that says 'unexpected 'is' (T_STRING) – Sohail Arif Nov 03 '15 at 12:10
Check this example http://www.mysqltutorial.org/php-querying-data-from-mysql-table/ you will get an idea – Ninju Nov 03 '15 at 12:15
I've never worked with PDO before, so it's a bit difficult for me to grasp so quickly. I'm used to the old method. Isn't there anyway to prevent an SQL injection using the older method? – Sohail Arif Nov 03 '15 at 12:18
otherwise you can use mysql_real_escape_string .$firstname = mysql_real_escape_string($firstname); – Ninju Nov 03 '15 at 12:20
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/94093/discussion-between-sohail-arif-and-ninju). – Sohail Arif Nov 03 '15 at 12:21

score 1 · Answer 2 · edited May 23 '17 at 11:51

1

Use prepared statements, this will solve the problem in any case. In this way the user input is beeing send seperatly and has no way of tampering with the Query.

http://php.net/manual/de/mysqli.quickstart.prepared-statements.php

Or How can I prevent SQL injection in PHP?

edited May 23 '17 at 11:51

Community

1
1

answered Nov 03 '15 at 11:33

JRsz

2,891
4
28
44

PHP - Preventing SQL Injection on INSERT STATEMENT

2 Answers2