How to prevent SQL injection in PHP?

I came to know that it is very important to stop SQL injections for security reasons. Please let me know how to do that using PHP.

Comments

  • youtube.com/watch?v=L41oWB4I1po and there are more parts, you can check from there

  • You can use prepared statements and parameterized queries. These are separately processed by the database server which will prevent injections.

    There are 2 methods.

    1. mysqli

      $stmt = $dbConnection->prepare('SELECT * FROM companies WHERE name = ?');
      $stmt->bind_param('s', $name);
      
      $stmt->execute();
      
      $result = $stmt->get_result();
      while ($row = $result->fetch_assoc()) {
              // process the data of $row
      }
      
    2. PDO

      $stmt = $pdo->prepare('SELECT * FROM companies WHERE name = :name');
      
      $stmt->execute(array('name' => $name));
      
      foreach ($stmt as $row) {
              // process the data of $row
      }
      
  • You also can prevent SQL Injection with Prepared Statements with mySQLi

    mySQLi SELECT Query

    $name = $_GET['username'];

    if ($stmt = $mysqli->prepare("SELECT password FROM tbl_users WHERE name=?")) {

    // Bind a variable to the parameter as a string. 
    $stmt->bind_param("s", $name);
    
    // Execute the statement.
    $stmt->execute();
    
    // Get the variables from the query.
    $stmt->bind_result($pass);
    
    // Fetch the data.
    $stmt->fetch();
    
    // Display the data.
    printf("Password for user %s is %s\n", $name, $pass);
    
    // Close the prepared statement.
    $stmt->close();
    

    }

    mySQLi INSERT Query.

    $name = $_GET['username'];
    $password = $_GET['password'];

    if ($stmt = $mysqli->prepare("INSERT INTO tbl_users (name, password) VALUES (?, ?)")) {

    // Bind the variables to the parameter as strings. 
    $stmt->bind_param("ss", $name, $password);
    
    // Execute the statement.
    $stmt->execute();
    
    // Close the prepared statement.
    $stmt->close();
    

    }

    mySQLi UPDATE Query.

    $name = $_GET['username'];
    $password = $_GET['password'];

    if ($stmt = $mysqli->prepare("UPDATE tbl_users SET password = ? WHERE name = ?")) {

    // Bind the variables to the parameter as strings. 
    $stmt->bind_param("ss", $password, $name);
    
    // Execute the statement.
    $stmt->execute();
    
    // Close the prepared statement.
    $stmt->close();
    

    }

    mySQLi DELETE Query.

    $name = $_GET['username'];
    $password = $_GET['password'];

    if ($stmt = $mysqli->prepare("DELETE FROM tbl_users WHERE name = ?")) {

    // Bind the variable to the parameter as a string. 
    $stmt->bind_param("s", $name);
    
    // Execute the statement.
    $stmt->execute();
    
    // Close the prepared statement.
    $stmt->close();
    

    }

    Hope this helps!

  • Here is my solution when i'm coding my websites with PHP.

    1. Creat a new .php file and named it as whatever you want. Mine is AntiSQLInjection.php

    `<?php $cautruyvan = $_SERVER['QUERY_STRING']; $tukhoa = array('chr(', 'chr=', 'chr%20', '%20chr', 'wget%20', '%20wget', 'wget(', 'cmd=', '%20cmd', 'cmd%20', 'rush=', '%20rush', 'rush%20', 'union%20', '%20union', 'union(', 'union=', 'echr(', '%20echr', 'echr%20', 'echr=', 'esystem(', 'esystem%20', 'cp%20', '%20cp', 'cp(', 'mdir%20', '%20mdir', 'mdir(', 'mcd%20', 'mrd%20', 'rm%20', '%20mcd', '%20mrd', '%20rm', 'mcd(', 'mrd(', 'rm(', 'mcd=', 'mrd=', 'mv%20', 'rmdir%20', 'mv(', 'rmdir(', 'chmod(', 'chmod%20', '%20chmod', 'chmod(', 'chmod=', 'chown%20', 'chgrp%20', 'chown(', 'chgrp(', 'locate%20', 'grep%20', 'locate(', 'grep(', 'diff%20', 'kill%20', 'kill(', 'killall', 'passwd%20', '%20passwd', 'passwd(', 'telnet%20', 'vi(', 'vi%20', 'insert%20into', 'select%20', 'nigga(', '%20nigga', 'nigga%20', 'fopen', 'fwrite', '%20like', 'like%20', '$_request', '$_get', '$request', '$get', '.system', 'HTTP_PHP', '&aim', '%20getenv', 'getenv%20', 'new_password', '&icq','/etc/password','/etc/shadow', '/etc/groups', '/etc/gshadow', 'HTTP_USER_AGENT', 'HTTP_HOST', '/bin/ps', 'wget%20', 'uname\x20-a', '/usr/bin/id', '/bin/echo', '/bin/kill', '/bin/', '/chgrp', '/chown', '/usr/bin', 'g\+\+', 'bin/python', 'bin/tclsh', 'bin/nasm', 'perl%20', 'traceroute%20', 'ping%20', '.pl', '/usr/X11R6/bin/xterm', 'lsof%20', '/bin/mail', '.conf', 'motd%20', 'HTTP/1.', '.inc.php', 'config.php', 'cgi-', '.eml', 'file\://', 'window.open', '<SCRIPT>', 'javascript\://','img src', 'img%20src','.jsp','ftp.exe', 'xp_enumdsn', 'xp_availablemedia', 'xp_filelist', 'xp_cmdshell', 'nc.exe', '.htpasswd', 'servlet', '/etc/passwd', 'wwwacl', '~root', '~ftp', '.js', '.jsp', 'admin_', '.history', 'bash_history', '.bash_history', '~nobody', 'server-info', 'server-status', 'reboot%20', 'halt%20', 'powerdown%20', '/home/ftp', '/home/www', 'secure_site, ok', 'chunked', 'org.apache', '/servlet/con', '', 'sql=');

    $kiemtra = str_replace($tukhoa, '*', $cautruyvan);

    if ($cautruyvan != $kiemtra)
    {
    $cremotead = $_SERVER['REMOTE_ADDR'];
    $cuseragent = $_SERVER['HTTP_USER_AGENT'];

      die( "Phat hien co su tan cong! <br /><br /><b>Viec tan cong nay da bi ngan chan va se duoc ghi nhan lai:</b><br />$cremotead - $cuseragent" );
    }
    

    ?>`

    1. Add these code in the file which you want to be protected.

    <?php include "AntiSQLInjection.php"; ?>

    Hope it helps all you guys!

    If you want to learn more about website programming, visite my own blog at Thiết kế Website theo yêu cầu.

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories