database gives all tables when no value entered.


#1

so i have a textbox on my site where my customers can enter a unique id to pull there account information and this works great what isn’t so great is if no value is entered into the textbox the results return every customer in the database. how can i prevent this from happening?

[php]

<?php if (isset($_POST['uid'])) { define('DB_NAME', 'accounts'); define('DB_USER', 'root'); define('DB_PASSWORD', ''); define('DB_HOST', 'localhost'); $conn = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME); if (!$conn) { die('Could not connect: ' . mysqli_connect_error()); } $uid = mysqli_real_escape_string($conn, $_POST['uid']); $sql = "SELECT * FROM `user` WHERE `UID` LIKE '%$uid%'"; $result=mysqli_query($conn, $sql); if (!$result) { echo 'There are no results for your search'; } else { echo ""; while($row = mysqli_fetch_array($result)) { echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; } echo "
UID Name Address Telephone Email Last Clean Next Clean Balance
" . $row['UID'] . "" . $row['Name'] . "" . $row['Address'] . "" . $row['Telephone'] . "" . $row['Email'] . "" . $row['Lclean'] . "" . $row['Nclean'] . "" . $row['Balance'] . "
"; } mysqli_close($conn); } else { ?>
Enter your UID here:

<?php } // end not submitted ?>

[/php]

thanks in advance.


#2

A simple way is to add required to the html input but you should also check the input is valid before doing your database query. You can also set a minimum length to your input and also do a check that it is of a suitable format before querying the database.


#3

prepared statements…

I wouldn’t use a like in this case either, too much ability to see an account that is not theirs.


#4

Allowing your web site to display customer’s phone and email address to anyone, or a bot script, who can feed it a range of all possible id values is not a good idea. You must keep your customer information safe.

Since you already have an email address, creating a login system, using the email and either a customer generated password or your randomly generated user id, is fairly easy and will secure your customer information. This will limit access to your site to only a logged in customer and once logged in, your code would use the logged in user’s id to display only the correct account information.

Speaking of security, You should NOT unconditionally output database statement errors on a live/public site, since hackers can trigger errors and get things like the database connection username and your domain root path on the server, which often contains your hosting account username. You should instead log database statement errors when on a live/public site. The easiest way of dong this, which will actually reduce the amount of code too, is to use exceptions to handle database statement errors and let php catch any exception, where it will use its error_reporting, display_errors, and log_errors settings to control what happens with the actual error information. When learning, developing, and debugging code/queries, you can display the information by setting php’s display_errors to ON. When on a live/public server, you would set display_errors to OFF and set log_errors to ON…

To enable exceptions for the mysqli extension, add the following line before the point where you make the database connection, and remove the existing error handling logic from your code -

[php]mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);[/php]

Also, switching to prepared queries, where a place-holder is put into the query statement for each piece of data and you supply the actual value when the query gets executed, will both simplify your code (eliminates the …_escape_string() statement) and makes your code more secure (if the character set that php is using for the …_escape_string() statement isn’t the same one that you are using for your database tables, sql injection is still possible and a hacker can use your code to get and display the content of any of your database tables.)


#5

ill be honest i dont understand a thing when it comes to php the stuff i have got so far was taken from a tutorial so again if im honest fixing it is more than likely beyond me, not to mention when setting up my database with my host and editing the file to point to the new database im getting a access denied error which for the life of me i cant fix because im using the right credentials and still nothing.


#6

what kind of customers are they? what kind of business? Is it yours, or do you work for someone?

It bothers me when security is taken so lightly in a business application, because breaches are not uncommon. Security should be a high priority that your customers assume is important to the people they deal with.


#7

Im a window cleaner self employed, majority of my customers are elderly and not tech savvy (abit like myself) hence the need for something simple like a uid that easily rememberable, alot of the time i dont have emails or telephone numbers for the customers so i could do away with that information.


#8

I wouldn’t call a uid or a guid memorable. I would get phone numbers and use it, like the stores do with their reward programs. Also, be selective in what you are actually showing.