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. stackoverflow.com/questions/60174/… — Tintu C Raju
– Tintu C Raju, Commented Nov 3, 2015 at 11:30
Basic debugging: echo your query before executing it. What is displayed? — Jocelyn
– Jocelyn, Commented Nov 3, 2015 at 14:59

Ninju · Accepted Answer · 2015-11-03 11:55:29Z

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 3, 2015 at 11:55

answered Nov 3, 2015 at 11:31

Ninju

2,5322 gold badges16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Sohail Arif Over a year ago

Hi Ninju, where you've placed ($conn is a PDO object) - I'm getting an error saying unexpected 'is'.. why is this?

Ninju Over a year ago

i have edited my answer.Using PDO to access a MySQL database real prepared statements are not used by default.

Sohail Arif Over a year ago

but what if I've written 'include("dbconfig.php")' on top of my php file?

Ninju Over a year ago

You need to set the PDO connection in dbconfig.php.

Sohail Arif Over a year ago

Ok, I'll try that now.

|

Community · Accepted Answer · 2017-05-23 11:51:58Z

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, 2017 at 11:51

CommunityBot

11 silver badge

answered Nov 3, 2015 at 11:33

JRsz

2,9414 gold badges32 silver badges49 bronze badges

Collectives™ on Stack Overflow

PHP - Preventing SQL Injection on INSERT STATEMENT

2 Answers 2

10 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related