What is SQL injection and how to prevent it?

When working with databases, one of the most common security vulnerabilities in web applications is definitely SQL injection attack. Malicious users can insert SQL query into the input data you’re using in your SQL queries and instead unwanted behavior happens.

SQL injection example with PDO

// GET data is sent through URL: http://example.com/get-user.php?id=1 OR id=2;
$id = $_GET['id'] ?? null;

// You are executing your application as usual
// Connect to a database
$dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', 'dbusername', 'dbpassword');

// Select user based on the above ID
// bump! Here SQL code GET data gets injected in your query. Be careful to avoid
// such coding and use prepared statements instead
$sql = "SELECT username, email FROM users WHERE id = " . $id;

foreach ($dbh->query($sql) as $row) {
    printf ("%s (%s)\n", $row['username'], $row['email']);
}

Just imagine worst case scenarios with injected SQL:

"'DELETE FROM users */"

How to avoid SQL injection in above example? Use prepared statements:

$sql = "SELECT username, email FROM users WHERE id = :id";

$sth = $dbh->prepare($sql, [PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY]);
$sth->execute([':id' => $id]);
$users = $sth->fetchAll();

mysqli example

When using MySQL database quite you can also use mysqli with prepared statements, or mysqli_real_escape_string() function, however you can just use more advanced PDO.

// get data is sent through url for example, http://example.com/get-user.php?id=1 OR id=2;
$id = $_GET['id'] ?? null;

// in your code you are executing your application as usual
$mysqli = new mysqli('localhost', 'db_user', 'db_password', 'db_name');

if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}

// bump! sql injected code gets inserted here. Be careful to avoid such coding
// and use prepared statements instead
$query = "SELECT username, email FROM users WHERE id = " . $id;

if ($result = $mysqli->query($query)) {
    // fetch object array
    while ($row = $result->fetch_row()) {
        printf ("%s (%s)\n", $row[0], $row[1]);
    }

    // free result set
    $result->close();
} else {
    die($mysqli->error);
}

Let’s fix this with prepared statements. They are more convenient because mysqli_real_escape_string() doesn’t apply quotes (it only escapes it).

// get data is sent through url for example, http://example.com/get-user.php?id=1 OR id=2;
$id = $_GET['id'] ?? null;

// in your code you are executing your application as usual
$mysqli = new mysqli('localhost', 'db_user', 'db_password', 'db_name');

if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}

// bump! sql injected code gets inserted here. Be careful to avoid such coding
// and use prepared statements instead
$query = "SELECT username, email FROM users WHERE id = ?";

$stmt = $mysqli->stmt_init();

if ($stmt->prepare($query)) {
    $stmt->bind_param("i", $id);
    $stmt->execute();
    $result = $stmt->get_result();
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
        printf ("%s (%s)\n", $row[0], $row[1]);
    }
}

See also

Other useful reading to check out:

• OWASP
• SQL injection - a community paradoxon
• Bobby Tables - A guide to preventing SQL injection.

PHP Debug In Browser Console

When debugging in PHP there are a few techniques you can use, you could use something like Xdebug which will allow you to step through your code at run time and you can see exactly what the code is doing. Xdebug will allow you to step into function and make sure the variables are being set as the should be.

Your other option is to output the code in the browser and exit the script so it just displays what you want to debug and nothing else, something similar to this.

echo '

';
print_r($debug_array);
echo '

';
exit;
?>

This will allow you to see exactly what is in this variable at a certain time of running your code.

But what if you want to view the rest of the page and debug at the sametime, you can simply print the variable without the exit in your code. But then you get the problem of the print being on the page which could either break your design or the debug will be displayed in the design.

Your other option is to write debug into a log file, this can be done with a debug class which writes to a debug log file, allowing you to view all your variables and not breaking your design.

If you don't want to send your data to a log file you can also try this neat little trick and output debug data into the browser debug console.

Here is a snippet of a function you can use to output PHP data to the browser console.

/**
 * Send debug code to the Javascript console
 */ 
function debug_to_console($data) {
    if(is_array($data) || is_object($data))
	{
		echo("");
	} else {
		echo("");
	}
}
?>

Golden Rules of programming

1 . The AND operator is used when we use (=) and the operator OR is used when we use (!=)
2. True ( || )     False ( && )

Comments urs.

How would you sort an array of strings to their natural case-insensitive order, while maintaing their original index association?

For example, the following array:

array(

                '0' => 'z1',

                '1' => 'Z10',

                '2' => 'z12',

                '3' => 'Z2',

                '4' => 'z3',

)

After sorting, should become:

array(

                '0' => 'z1',

                '3' => 'Z2',

                '4' => 'z3',

                '1' => 'Z10',

                '2' => 'z12',

)

The trick to solving this problem is to use three special flags with the standard asort() library function:

asort($arr, SORT_STRING|SORT_FLAG_CASE|SORT_NATURAL)

The function asort() is a variant of the standard function sort() that preserves the index association. The three flags used above SORT_STRING, SORT_FLAG_CASE and SORT_NATURAL forces the sort function to treat the items as strings, sort in a case-insensitive way and maintain natural order respectively.

Note: Using the natcasesort() function would not be a correct answer, since it would not maintain the original index association of the elements of the array.

DataTable With Codeigniter

click the below Link to see the Code.

https://drive.google.com/file/d/0B9cPcFcKq6vnU3dwWDEtYlB5RDg/view?usp=sharing

How to Secure PHP Web Applications and Prevent Attacks?

As a developer you must know how to build a secure and bulletproof application. Your duty is to prevent security attacks and secure your application.

Checklist of PHP and Web Security Issues

Make sure you have these items sorted out when deploying your application into production environment:

1. ✔ Cross Site Scripting (XSS)
2. ✔ Injections
   • ✔ SQL Injection
   • ✔ Directory Traversal (Path Injection)
   • ✔ Command Injection
   • ✔ Code Injection
3. ✔ Cross Site Request Forgery (XSRF/CSRF)
4. ✔ Public Files
5. ✔ Passwords
6. ✔ Uploading Files
7. ✔ Session Hijacking
8. ✔ Remote File Inclusion
9. ✔ PHP Configuration
   • ✔ Error Reporting
   • ✔ Exposing PHP Version
   • ✔ Remote Files
   • ✔ open_basedir
   • ✔ Session Settings
10. ✔ Use HTTPS
11. ✔ Things Not Listed

Cross Site Scripting (XSS)

XSS attack happens where client side code (usually JavaScript) gets injected into the output of your PHP script.

// GET data is sent through URL: http://example.com/search.php?search=
$search = $_GET['search'] ?? null;
echo 'Search results for '.$search;

// This can be solved with htmlspecialchars
$search = htmlspecialchars($search, ENT_QUOTES, 'UTF-8');
echo 'Search results for '.$search;

• ENT_QUOTES is used to escape single and double quotes beside HTML entities
• UTF-8 is used for pre PHP 5.4 environments (now it is default). In some browsers some characters might get pass thehtmlspecialchars().

Injections

SQL Injection

When accessing databases from your application, SQL injection attack can happen by injecting malicious SQL parts into your existing SQL statement.

• More details available in “What is SQL injection and how to prevent it?” FAQ.

Directory Traversal (Path Injection)

Directory traversal attack is also known as ../ (dot, dot, slash) attack. It happens where user supplies input file names and can traverse to parent directory. Data can be set as index.php?page=../secret or /var/www/secret or something more catastrophic:

$page = $_GET['page'] ?? 'home';

require $page;
// or something like this
echo file_get_contents('../pages/'.$page.'.php');

In such cases you must check if there are attempts to access parent or some remote folder:

// Checking if the string contains parent directory
if (strstr($_GET['page'], '../') !== false) {
    throw new \Exception("Directory traversal attempt!");
}

// Checking remote file inclusions
if (strstr($_GET['page'], 'file://') !== false) {
    throw new \Exception("Remote file inclusion attempt!");
}

// Using whitelists of pages that are allowed to be included in the first place
$allowed = ['home', 'blog', 'gallery', 'catalog'];
$page = (in_array($page, $allowed)) ? $page : 'home';
echo file_get_contents('../pages/'.$page.'.php');

Command Injection

Be careful when dealing with commands executing functions and data you don’t trust.

exec('rm -rf '.$GET['path']);

Code Injection

Code injection happens when malicious code can be injected in eval() function, so sanitize your data when using it:

eval('include '.$_GET['path']);

Cross Site Request Forgery (XSRF/CSRF)

Cross site request forgery or one click attack or session riding is an exploit where user executes unwanted actions on web applications.

Public Files

Make sure to move all your application files, configuration files and similar parts of your web application in a folder that is not publicly accessible when you visit URL of web application. Some file types (for example .yml files) might not be processed by your web server and user can view them online.

Example of good folder structure:

app/
  config/
    parameters.yml
  src/
public/
  index.php
  style.css
  javascript.js
  logo.png

Configure web server to serve files from public folder instead of your application root folder. Public folder contains the front controller (index.php). In case web server gets misconfigured and fails to serve PHP files properly only source code of index.php will be visible to public.

• More details is available in the dedicated FAQ: How to Use Configuration in PHP Applications?

Passwords

When working with user’s passwords hash them properly with password_hash() function.

• More details is available in “How to work with users’ passwords and how to securely hash passwords in PHP?” FAQ.

Uploading Files

A lot of security breaches happen where users can upload a file on server. Make sure you go through all the vulnerabilities of uploading files such as renaming uploaded file, moving it to publicly unaccessible folder, checking file type and similar. Since there are a lot of issues to check here, more information is located in the separate FAQ:

• How to securely upload files with PHP? FAQ.

Session Hijacking

Session hijacking is an attack where attacker steals session ID of a user. Session ID is sent to server where $_SESSION array gets populated based on it. Session hijacking is possible through an XSS attack or if someone gains access to folder on server where session data is stored.

Remote File Inclusion

Remote file inclusion attack (RFI) means that attacker can include custom scripts:

$page = $_GET['page'] ?? 'home'

require $page . '.php';

In above code $_GET can be set to a remote file http://yourdomain.tld/index.php?page=http://example.com/evilscript

Make sure you disable this in your php.ini unless you know what you’re doing:

; Disable including remote files
allow_url_fopen = off
; Disable opening remote files for include(), require() and include_once() functions.
; If above allow_url_fopen is disabled, allow_url_include is also disabled.
allow_url_include = off

PHP Configuration

Always keep installed PHP version updated. You can use versionscan to check for possible vulnerabilities of your PHP version. Update open source libraries and applications and maintain web server.

Here are some of the important settings from php.ini that you should check out. You can also use iniscan to scan your php.ini files for best security practices.

Error Reporting

In your production environment you must always turn off displaying errors to screen. If errors occur in your application and they are visible to the outside world, attacker can get valuable data for attacking your application. display_errors and log_errors directives inphp.ini file:

; Disable displaying errors to screen
display_errors = off
; Enable writing errors to server logs
log_errors = on

• More information in the How to show errors in PHP FAQ.

Exposing PHP Version

PHP version is visible in HTML headers. You might want to consider hiding PHP version by turning off expose_php directive and prevent web server to send back header X-Powered-By:

expose_php = off

Remote Files

In most cases it is important to disable access to remote files:

; disabled opening remote files for fopen, fsockopen, file_get_contents and similar functions
allow_url_fopen =  0
; disabled including remote files for require, include ans similar functions
allow_url_include = 0

open_basedir

This settings defines one or more directories (subdirectories included) where PHP has access to read and write files. This includes file handling (fopen, file_get_contents) and also including files (include, require):

open_basedir = "/var/www/test/uploads"

Session Settings

• session.use_cookies and session.use_only_cookies
  PHP is by default configured to store session data on the server and a tracking cookie on client side (usually called PHPSESSID) with unique ID for the session.

; in most cases you'll want to enable cookies for storing session
session.use_cookies = 1
; disabled changing session id through PHPSESSID parameter (e.g foo.php?PHPSESSID=)
session.use_only_cookies = 1
session.use_trans_sid = 0
; rejects any session ID from user that doesn't match current one and creates new one
session.use_strict_mode = 0

• session.cookie_httponly
  If the attacker somehow manages to inject Javascript code for stealing user’s current cookies (the document.cookie string), theHttpOnly cookie you’ve set won’t show up in the list.

session.cookie_httponly = 1

• session.cookie_domain
  This sets the domain for which cookies apply. For wildcard domains you can use .example.com or set this to the domain it should be applied. By default it is not enabled, so it is highly recommended for you to enable it:

session.cookie_domain = example.com

• session.cookie_secure
  For HTTPS sites this accepts only cookies sent over HTTPS. If you’re still not using HTTPS, you should consider it.

session.cookie_secure = 1

Use HTTPS

HTTPS is a protocol for secure communication over network. It is highly recommended that you enable it on all sites. Read more about HTTPS in the dedicated FAQ: How to Install SSL Certificate and Enable HTTPS.

What is Next?

Above we’ve introduced many security issues. Security, attacks and vulnerabilities are continuously evolving. Take time and check some good resources to learn more about security and turn this check list into a habit:

• General:
  • Awesome AppSec - A curated list of resources for learning about application security.
  • OWASP - The Open Web Application Security Project, organization focused on improving security of software.
  • Security Guide for Developers
• PHP focused:
  • PHP Manual - A must read security chapter in official documentation.
  • Codecourse videos - Demos and advice on the most common PHP security areas.
  • DVWA, Damn Vulnerable Web Application - Example of unsecure web application to test your skills and tools.
  • OWASP PHP Security Cheat Sheet - Basic PHP security tips for developers and administrators.
  • Securing PHP - Website and books with basic topics and specific cases in authentication/authorization and exploit prevention.
  • SensioLabs Security - SensioLabs Security Advisories Checker for checking your PHP project for known security issues
  • The most forgotten web vulnerabilities - Recommended PDF article.
  • websec.io - Dedicated to educating developers about security with topics relating to general security fundamentals, emerging technologies and PHP-specific information.
• Tools:
  • iniscan - A php.ini scanner for best security practices.
  • Kali Linux - Penetration testing Linux distribution.
  • Observatory by Mozilla - Online security checker.
  • versionscan - PHP version scanner for reporting possible vulnerabilities.
  • Roave Security Advisories - This package ensures that your application doesn’t have installed dependencies with known security vulnerabilities.
  • WebSecTools - List of useful web security related tools.
  • OWASP Zed Attack Proxy - Free security tool, available also on GitHub.