PHP protect from SQL Injection and XSS attacks

  • (2 Pages)
  • +
  • 1
  • 2

26 Replies - 1611 Views - Last Post: 12 June 2016 - 01:32 AM Rate Topic: -----

#1 fearfulsc2   User is offline

  • D.I.C Head

Reputation: 9
  • View blog
  • Posts: 179
  • Joined: 25-May 16

PHP protect from SQL Injection and XSS attacks

Posted 06 June 2016 - 01:29 PM

Hey everyone, I have created a PHP web application that allows people of different permissions to do certain things such as read-only, add, update, or delete. That works pefectly. However, it came across my mind that I have not done anything to prevent against SQL injection or XSS attacks. I have little to no knowledge on how to do that. I'll show what I have and maybe a little guidance can help me learn how to do some type of hardening on my tables.

<?php
        //connects to the Database
        include_once("sqlConnect.php");
        //Checks the previous page for the choice selected from the Dropdown menu
        if(isset($_POST['currentUser']))
        {
                $currentUser = $_POST['currentUser'];
                $sql5 = "SELECT * FROM engineers WHERE UserID='$currentUser'";
                $result3 = mysqli_query($conn, $sql5);
                $row3 = mysqli_fetch_array($result3);
                $Access = $row3["AccessCode"];
                if ($Access <= 5)
                {
                        if(isset($_POST['engineers']))
                        {
                                $UserID = $_POST['engineers'];
                                //SQL command to pull information from the table where Sales Engineers are
                                $sql = "SELECT * FROM engineers WHERE UserID='$UserID'";
                                //The result is saved here
                                $result = mysqli_query($conn, $sql);
                                //Will fetch the information
                                //Slightly redundant since we will only be selecting only one
                                //entry at a time
                                while($row = mysqli_fetch_array($result))
                                {
                                        $EmployeeID = $row["EmployeeID"];
                                        $FirstName = $row["FirstName"];
                                        $LastName = $row["LastName"];
                                        $Extension = $row["Extension"];
                                        $Telephone = $row["Telephone"];
                                        $Email = $row["Email"];
                                        $PPhone = $row["PersonalPhone"];
                                        $MPhone = $row["Mobile"];
                                        $TeleAgent = $row["TelephoneAgent"];
                                        $Department = $row["Department"];
                                        $Access = $row["AccessCode"];
                                        $UserID = $row["UserID"];
                                }
                        }
                }
                 else
                {
                        echo "<script> alert('You do not have permission to edit');
                                window.history.back();
                                </script>";
                }
        }



This is just a snippet of what I have. Pretty much, I will have a page that will add and I will also have another page to help delete. Same kind of thing but for different queries. Does anyone know what I can do to help make it harder to do SQL injection or XSS? Please don't give me code unless it's something that is really obvious that I should be doing. I want to learn, so a snippet here or there or a good article that can help me will be better for me. Thank you! By the way, I tried looking this up on google but can't really understand what the person who wrote the article was doing.

Also, I don't know if this is the right thread to post this on.

Is This A Good Question/Topic? 0
  • +

Replies To: PHP protect from SQL Injection and XSS attacks

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14417
  • View blog
  • Posts: 57,803
  • Joined: 12-June 08

Re: PHP protect from SQL Injection and XSS attacks

Posted 06 June 2016 - 01:31 PM

Two words: parameterized queries.
Was This Post Helpful? 0
  • +
  • -

#3 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2669
  • View blog
  • Posts: 10,654
  • Joined: 03-December 12

Re: PHP protect from SQL Injection and XSS attacks

Posted 06 June 2016 - 01:38 PM

Parameterized queries for the SQL injection. using htmlentities for the output from databases. and

various headers to give some security.
header("X-XSS-Protection: 0");
Was This Post Helpful? 0
  • +
  • -

#4 fearfulsc2   User is offline

  • D.I.C Head

Reputation: 9
  • View blog
  • Posts: 179
  • Joined: 25-May 16

Re: PHP protect from SQL Injection and XSS attacks

Posted 06 June 2016 - 01:41 PM

So something like this?

if(isset($_POST['currentUser']))
        {
                $currentUser = $_POST['currentUser'];
                $sql5 = $dbh->prepare("SELECT * FROM engineers WHERE UserID='$currentUser'");
                if($sql5->execute(array($_POST['currentUser'])))
                {
                        echo "HELLO"; //just to see if the if statement is satisfied
                        while($row10 = $sql5->fetch())
                        {
                                print_r($row10);
                        }
                }




I tried running the page, but I seem to have an error somewhere

This post has been edited by fearfulsc2: 06 June 2016 - 01:42 PM

Was This Post Helpful? 0
  • +
  • -

#5 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2669
  • View blog
  • Posts: 10,654
  • Joined: 03-December 12

Re: PHP protect from SQL Injection and XSS attacks

Posted 06 June 2016 - 01:44 PM

there is a pdo tutorial in my signature. Read it.
Was This Post Helpful? 0
  • +
  • -

#6 fearfulsc2   User is offline

  • D.I.C Head

Reputation: 9
  • View blog
  • Posts: 179
  • Joined: 25-May 16

Re: PHP protect from SQL Injection and XSS attacks

Posted 06 June 2016 - 01:46 PM

Oh I didn't do PDO. I did Object-oriented mySQLi. Would I have to change my syntax to PDO?
Was This Post Helpful? 0
  • +
  • -

#7 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2669
  • View blog
  • Posts: 10,654
  • Joined: 03-December 12

Re: PHP protect from SQL Injection and XSS attacks

Posted 06 June 2016 - 01:50 PM

You can do prepared statements in mysqli,
did you look to see how?
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw   User is offline

  • Entwickler
  • member icon

Reputation: 6604
  • View blog
  • Posts: 26,908
  • Joined: 12-December 12

Re: PHP protect from SQL Injection and XSS attacks

Posted 06 June 2016 - 02:01 PM

Prepared statements and stored procedures :the docs

Survive The Deep End: PHP Security
Was This Post Helpful? 0
  • +
  • -

#9 fearfulsc2   User is offline

  • D.I.C Head

Reputation: 9
  • View blog
  • Posts: 179
  • Joined: 25-May 16

Re: PHP protect from SQL Injection and XSS attacks

Posted 07 June 2016 - 07:27 AM

I'm still not understanding. I tried this as a test and I can't seem to get the page to load.
 if(isset($_POST['currentUser']))
        {
                //echo "HELLO";  //if I uncomment, the page will say hello but nothing else
                $currentUser = $_POST['currentUser'];
                $stmt = $mysqli->prepare("SELECT * FROM engineers WHERE UserID=?");
                $stmt->bind_param("s", $currentUser);

                $stmt->execute();
                $stmt->bind_result($AccessCode);
                $stmt->fetch();
                printf("%s has access code %s\n", $currentUser, $AccessCode);
                $stmt->close();





And I also tried this just to see if anything would have changed. But still can't have the page load.

//echo "HELLO";
                $currentUser = $_POST['currentUser'];
                $stmt = mysqli_prepare($conn, "SELECT * FROM engineers WHERE UserID=?");
                mysqli_stmt_bind_param($stmt, "s", $currentUser);
                mysqli_stmt_execute($stmt);

                mysqli_stmt_bind_result($stmt, $AccessCode);

                mysqli_stmt_fetch($stmt);
                printf("%s has access code %s \n", $currentUser, $AccessCode);
                mysqli_stmt_close($stmt);



This post has been edited by fearfulsc2: 07 June 2016 - 07:36 AM

Was This Post Helpful? 0
  • +
  • -

#10 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2669
  • View blog
  • Posts: 10,654
  • Joined: 03-December 12

Re: PHP protect from SQL Injection and XSS attacks

Posted 07 June 2016 - 07:47 AM

turn error reporting on. If the page fails to load, you have a fatal error. Displaying those, allows you to fix the issue.
Was This Post Helpful? 0
  • +
  • -

#11 fearfulsc2   User is offline

  • D.I.C Head

Reputation: 9
  • View blog
  • Posts: 179
  • Joined: 25-May 16

Re: PHP protect from SQL Injection and XSS attacks

Posted 07 June 2016 - 11:20 AM

I started to do this and was able to get one value to be pulled. I can't figure out how to do a SELECT * command for one User. Is that possible? This is what I have. Also, is closing and initializing redundant in my code?

if(isset($_POST['currentUser']))
{
        $currentUser = $_POST['currentUser'];
        $stmt = mysqli_stmt_init($conn);
        if(mysqli_stmt_prepare($stmt, 'SELECT AccessCode FROM engineers WHERE UserID=?'))
        {
                mysqli_stmt_bind_param($stmt, "s", $currentUser);
                mysqli_stmt_execute($stmt);
                mysqli_stmt_bind_result($stmt, $Access);
                mysqli_stmt_fetch($stmt);
                printf("%s has access code %s\n", $currentUser, $Access);
                mysqli_stmt_close($stmt);
        }

        if(isset($_POST['engineers']))
        {
                $UserID = $_POST['engineers'];
                $stmt = mysqli_stmt_init($conn);
                if(mysqli_stmt_prepare($stmt, 'SELECT * FROM engineers WHERE UserID=?'))
                {
                        mysqli_stmt_bind_param($stmt, "s", $UserID);
                        mysqli_stmt_execute($stmt);
                }
                //SQL command
        }




Was This Post Helpful? 0
  • +
  • -

#12 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2669
  • View blog
  • Posts: 10,654
  • Joined: 03-December 12

Re: PHP protect from SQL Injection and XSS attacks

Posted 07 June 2016 - 11:37 AM

You can accomplish the task with a single query, if that is what you mean.
Was This Post Helpful? 0
  • +
  • -

#13 fearfulsc2   User is offline

  • D.I.C Head

Reputation: 9
  • View blog
  • Posts: 179
  • Joined: 25-May 16

Re: PHP protect from SQL Injection and XSS attacks

Posted 07 June 2016 - 12:32 PM

Well, the goal was to turn this into a prepared statement. I'm super new to it, so I'm in a little bit of a rut right now. I managed to be able to have the query output a single value for something, but now I want to be able to select everything and have it output. I tried doing a mysqli_stmt_fetch_array kind of thing but it didn't work(I don't know if that exists)

This is the original of what I had
if(isset($_POST['currentUser']))
        {
                $currentUser = $_POST['currentUser'];
                $sql5 = "SELECT * FROM engineers WHERE UserID='$currentUser'";
                $result3 = mysqli_query($conn, $sql5);
                $row3 = mysqli_fetch_array($result3);
                $Access = $row3["AccessCode"];
                if ($Access <= 5)
                {
                        if(isset($_POST['engineers']))
                        {
                                $UserID = $_POST['engineers'];
                                //SQL command to pull information from the table where Sales Engineers are
                                $sql = "SELECT * FROM engineers WHERE UserID='$UserID'";
                                //The result is saved here
                                $result = mysqli_query($conn, $sql);
                                //Will fetch the information
                                //Slightly redundant since we will only be selecting only one
                                //entry at a time
                                while($row = mysqli_fetch_array($result))
                                {
                                        $EmployeeID = $row["EmployeeID"];
                                        $FirstName = $row["FirstName"];
                                        $LastName = $row["LastName"];
                                        $Extension = $row["Extension"];
                                        $Telephone = $row["Telephone"];
                                        $Email = $row["Email"];
                                        $PPhone = $row["PersonalPhone"];
                                        $MPhone = $row["Mobile"];
                                        $TeleAgent = $row["TelephoneAgent"];
                                        $Department = $row["Department"];
                                        $Access = $row["AccessCode"];
                                        $UserID = $row["UserID"];

                                }
                        }

                }
                else
                {
                        echo "<script> alert('You do not have permission to edit');
                                window.history.back();
                                </script>";
                }
        }



What you see in the above comments is the prepared statement, but I still don't fully understand it.
Was This Post Helpful? 0
  • +
  • -

#14 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2669
  • View blog
  • Posts: 10,654
  • Joined: 03-December 12

Re: PHP protect from SQL Injection and XSS attacks

Posted 07 June 2016 - 02:23 PM

Check here: Prepared Statements with MySQLi

Post#11 was closer, but you do nothing with your second sql statement.

Why are running multiple queries? You can grab the information, then based on who requested the data, return what is needed. Outside of the prepared statement hurdle, what are you trying to accomplish?
Was This Post Helpful? 0
  • +
  • -

#15 fearfulsc2   User is offline

  • D.I.C Head

Reputation: 9
  • View blog
  • Posts: 179
  • Joined: 25-May 16

Re: PHP protect from SQL Injection and XSS attacks

Posted 08 June 2016 - 06:09 AM

This is what I currently have in place: I have a page that asks a user to pick a username. They go to the next page where they have different options. One of those options is an option to see all the users in the department(s). If they click that button, a window will pop up but will have the information on which user opened that window.

When that window opens, it will display all the people in the department. Right next to each person, there will be an update and a delete button. Depending on their privileges, they will not be allowed to update or delete.

At the end of the page with all the people, there will be an add button that has the same restriction such as the update or delete. The way I check this is on the other page. So when they hit any one of those buttons, their username will be POSTed from a submit form where the next page will take that username and check their privileges in the database table.

If they have the right privileges, the page will display the user's(the user that they selected to update if they hit update) information where they can edit anything if they need to. Same will go for delete where it will delete the person who was selected. And for add, it's the same thing but will add someone into the database.

But I can't seem to do a SELECT * type of thing in the prepared statement as I was only able to test for a specific column in the table.

So my question is, can I do a prepared statement for a SELECT all command?
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2