1

I'm currently working on an IP-Address-Management. I created an auto-complete function into a textbox that shows ip-addresses that are in my SQL Database.

But I want to only show ip-addresses that are NOT in the Database. I want an ip address to be suggested to the user as soon as he starts writing the first octets, the suggested ip addresses should not be in the table and thus be freely selectable. For example, the user writes 168.0.0. and then all ip addresses that are not already in dspia_main should be available for selection via autocomplete.

I think it has to be a loop that goes from 1 to 255 (IP-Address rules) and checks if the ipv4 is in the database or not, if yes, than put it in the output array.

Here's my current code:

newip.php:

<!DOCTYPE html>
<html lang="en">
<head>
.......
<script>
 $(function() {
    $("#ip_v4").autocomplete({
        source: "fetchData.php",
    });
 });
</script>
</head>
<body>
<form action="newip.php" autocomplete="off" method="post">
    <label><b>IPv4-Adresse:*</b></label>
        <input type="text" pattern="^((\d{1,2}|1\d\d|2[0-4]\d|25[0-5])\.){3}(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])$" placeholder="z.B. 212.227.142.131" name="ip_v4" id="ip_v4" value="<?php echo $_POST['ip_v4'];?>" maxlength="15" required><br>
.......

EDIT:

  1. I still need to check if $term . $val is in Database
  2. I added a range from 0 to 255, but it also shows 192.1001, how can the range only apply per octet? fetchData.php:
<?php

if(!empty($_GET['term']))
{
include('db_conn.php');

$term = $_GET['term'];
$term = mysqli_real_escape_string($conn, $term);
$output = array();

$result = $conn->query("SELECT ip_v4 FROM dspia_main WHERE ip_v4 LIKE '" . $term . "%' AND state_id = '1' LIMIT 10");

$row=mysqli_fetch_array($result);


foreach(range(0,255) as $val) 
{
    $output[] = $term . $val;
}

mysqli_close($conn);

// output our results as JSON as jQuery expects
echo json_encode($output);
}
?>
AminCoD
  • 13
  • 5
  • 1
    So then first you need to implement PHP code so that it understands the rules of IP address ranges, so it can calculate all the possible addresses, and then eliminate the ones already in the DB – ADyson Feb 24 '23 at 13:19
  • **Warning:** Your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. **Never** insert unparameterised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. mysqli_real_escape_string is obsolete and doesn't guard against everything. – ADyson Feb 24 '23 at 13:20
  • https://phpdelusions.net/mysqli also contains good examples of writing safe SQL using mysqli. See also the [mysqli documentation](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) and this: [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) . Parameterising your queries will also greatly reduce the risk of accidental syntax errors as a result of un-escaped or incorrectly quoted input values. If you learnt your current technique from a tutorial or book, please don't use that resource again. – ADyson Feb 24 '23 at 13:20
  • Your code doesn't have to deal with IPv6 addresses? – KIKO Software Feb 24 '23 at 13:21
  • @KIKOSoftware ipv4 only for now :) – AminCoD Feb 24 '23 at 13:44
  • @ADyson it is only used internally and not publicly – AminCoD Feb 24 '23 at 13:48
  • 1
    And you can trust that everyone in your organisation is never going to be malicious, or accidentally host malware which can attack you? Being partially hidden is not a form of security. Implement some actual security, then there's no doubt. It's difficult at all either, in this case. – ADyson Feb 24 '23 at 13:54
  • 1
    I agree with ADyson. There's really no good excuses, apart from apathy, to not write secure code. Writing secure code is, if you always do it, as easy as writing insecure code. Practice makes perfect. – KIKO Software Feb 24 '23 at 14:01
  • @ADyson can you help me with the question with code? – AminCoD Mar 02 '23 at 08:07
  • Well, where are you stuck. Did you attempt the suggestion in my first comment? Do you first understand the rules of how IP address ranges are defined? – ADyson Mar 02 '23 at 09:20
  • @ADyson I added my changes in the post – AminCoD Mar 02 '23 at 13:20

1 Answers1

-2

if I understand correctly , you just need to pass the query that you already have as a subquery using the same table

//something like 
$sql = "Select ip_v4  
 FROM dspia_main 
  WHERE ip_v4  not in
 (
    SELECT ip_v4 FROM dspia_main WHERE ip_v4 LIKE '" . $term . "%' AND state_id = '1' 
 )"
PIBA
  • 19
  • 4