Services
Technology Web App Development Mobile App Development Windows App Development Database Development UI/UX Development Cloud Development SEO Digital Marketing Guest Posting Content Writing Artificial Intelligence Data Science ERP Development CRM Development CMS Development LMS Development Business Listing Blockchain Technology Startup Promotion AI Tools Integration
Products
Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller
Developer
Article Blog Forum Interview Beginner News Startup Quiz
Users
Career
Apply Job Internship
Pricing
Login Sign Up Get a Quote

forum

home / developersection / forums / how to prevent sql injection in php?
Ask Question

How to prevent SQL injection in PHP?

Anonymous User 2936 17 Apr 2013
Hi Everyone!

If user input is inserted into an SQL query directly, the application becomes vulnerable to SQL injection, like in the following example:

$unsafe_variable = $_POST['user_input'];

mysql_query("INSERT INTO table (column) VALUES ('" . $unsafe_variable . "')");
That's because the user can input something like value'); DROP TABLE table;--, making the query:

INSERT INTO table (column) VALUES('value'); DROP TABLE table;--')

What should one do to prevent this?

Thanks in advance!
php php
Updated 17 Apr 2013
Anonymous User
Anonymous User

Other

I am a content writter !

Message

1 Answers

AVADHESH PATEL
AVADHESH PATEL 17 Apr 2013
Report Abuse
Hi Jayprakash!

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.

You basically have two options to achieve this:

    Using PDO:

    $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');

    $stmt->execute(array(':name' => $name));

    foreach ($stmt as $row) {
        // do something with $row
    }

    Using mysqli:

    $stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
    $stmt->bind_param('s', $name);

    $stmt->execute();

    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) {
        // do something with $row
    }

PDO

Note that when using PDO to access a MySQL database real prepared statements are not used by default. To fix this you have to disable the emulation of prepared 

statements. An example of creating a connection using PDO is:

$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

I hope it resolve your problem!

advertising