my website is not secure

[UK-1991]

Hello I have created a website recently and my client is saying that your website is not secured I have easily created new tables and even a kid can get in to your database and modify your database.

is this because I have used this <a id="svgtriangle_f9" xlink:href="mainpage.php?apart=1&floor=9" class="portfolio-link" onmousemove="ShowTooltip(evt, 'Floor 09')" onmouseout="HideTooltip(evt)">

so what should be my approach and how can I secure it as there is no other way i can secure the parameters as i need to get the info like whne we clock on apartment i need to know where like on which apartment which floor he have clicked on so i can get the result for that apartment and floor number here is the example I have done

<?php
 $apart = $_GET["apart"];
$floor = $_GET["floor"];
$link  = "inner.php?apart=".$_GET["apart"]."&floor=".$_GET["floor"]."&room=";
            $query = mysqli_query($connection, "SELECT * FROM appartments WHERE apartment='$apart' AND room_no='{$_GET["room"]}' AND floor_no='$floor'");
            while($records = mysqli_fetch_assoc($query)) {
                $description  = $records["description"];
                $living_space = $records["living_space"];
                $total_area   = $records["total_area"];
                $room_id      = $records["id"];
                $map_image    = $records["map_image"];
            }


         ?>

Please share your ideas and your guidelines so I can secure my website

[diafol]

This is SQL Injection 101. Never, EVER place raw input into SQL. Please check out the PHP sticky:

https://www.daniweb.com/programming/web-development/threads/499320/common-issues-with-mysql-and-php

Item #2

Your client is right.

[UK-1991]

Okay then I am confused so like when ever I want to put a dynamic quesry like a variable we have to make our sql like this?

$query = mysqli_query($connection, "SELECT * FROM appartments WHERE apartment='.$apart.' AND room_no='{$_GET["room"]}' AND floor_no='.$floor.'");

okay now what will you suggest can you give me and point out my mistake I am totally confused about what you said to me raw input ?

[diafol]

Raw input is user supplied data that has not been sanitized, e.g. $_GET or $_POST.
When using this type of data, you should either:

A) sanitize it, or
B) use a prepared statement

The link I supplied (then go to item #2 in the list), shows how to create a simple prepared statement (PS) in PDO and MySQLi. Sure you can sanitize with mysqli_real_escape_string, but there's no need with PS.

Placing a {brace} around user supplied data does nothing with regard to sanitizing, it ust ensures that array data is parsed properly. You probably read Item #4 - but even that suggests not bracing, just using a PS.

BTW - there doesn't seem to be anything wrong with the link you created - just your handling of SQL queries (easily fixed).

[UK-1991]

here is my updated sql now is this the corect way or I have to improve the pdo is little bit confusing me.

so this is my updated sql query is it correct one ??

            $safe_apart = mysqli_real_escape_string($connection, $apart);
            $safe_floor = mysqli_real_escape_string($connection, $floor);
            $room_no    = mysqli_real_escape_string($connection, $_GET["room"]);

            $query = mysqli_query($connection, "SELECT * FROM appartments WHERE apartment='.$safe_apart.' AND room_no='.$room_no.' AND floor_no='.$safe_floor.'");

Secondly PDO is like this in example

$id = $_POST['id'];
$sql = "SELECT * FROM `table1` WHERE `id` = ?";
$stmt = pdo->prepare($sql);
$stmt->bindParam(1,$id,PDO::PARAM_INT); //1 = position of ?
$result = $stmt->execute();

so if we want to use a loop of while or array how do we call the data from the databases??

like in my case if i use a PDO statement how do i use it is this a correct way to use it ???

$sql = "SELECT * FROM appartments WHERE apartment='.$safe_apart.' AND room_no='.$room_no.' AND floor_no='.$safe_floor.'";
$stmt = pdo->prepare($sql);
$stmt->bindParam(1,$id,PDO::PARAM_INT); //1 = position of ?
$result = $stmt->execute();

$query = mysqli_query($connection, $result);
while($records = mysqli_fetch_assoc($query)) {
    $description  = $records["a"];
    $living_space = $records["b"];
    $total_area   = $records["c"];
    $room_id      = $records["d"];
    $map_image    = $records["e"];
}

I am also confused about this stament how is this worked though bindParam(1,$id,PDO::PARAM_INT)

Thank You

[jkon]

/**
 * I don't know if apartment in your table is 
 * VARCHAR or INT or its type
 */
$safe_apart = "test value";
$room_no = "test value";
$safe_floor = "test value";

$database = "test";
$username = "testuser";
$password = "testpassword";

$db = new PDO("mysql:host=localhost;dbname=".$database, $username, $password);
$statement = $db->prepare("SELECT * FROM appartments WHERE apartment = ? AND room_no = ? AND floor_no = ?");
$statement->execute(array($safe_apart,$room_no,$safe_floor));
$results = $statement->fetchAll(PDO::FETCH_ASSOC);
if(count($results) > 0)
{
    foreach($results as $row)
    {
        // DO ANYTHING
    }
}

If you have read the link that Diafol gave you you wouldn't have have this question