Tag Archives: security

Get Top URLs in SGUIL (with SecurityOnion Example)

Here’s a quick way to get a list of the top URLs used in a system that is monitoring traffic with SGUIL.  Login to the MySQL database and query the “event” table for signatures that have “URL” in them.  The example below is for SecurityOnion.

# In mysql, gets the top urls

# -A turns off "reading table information for completion of table and column names" for faster DB selection

mysql -uroot -A

use securityonion_db;

# Change date in the WHERE clause and number in LIMIT
# This query below retrieves the top 100 URLS after the date specified
SELECT event.signature,
FROM event
WHERE event.timestamp > '2013-03-01'
AND event.signature LIKE '%URL%'
GROUP BY event.signature
ORDER By count(*) desc
LIMIT 100;

This is a good quick way to find out what people are requesting.  However, because so many CDN’s, ad servers and trackers use multiple hostnames, you don’t get the big picture of what is coming from just the domain itself.  This is great if you are retrieving statistics to help you tune performance on your ad blocker or web proxy cache.

Creating MySQL Users and Granting Permissions

When should you create a new user in MySQL? That’s a question that you have to ask whenever you add a new application or restructure your data. I’ve been in places where every application connects as root, and obviously that’s very bad.

Only you know your situation. When I build web applications sometimes I’ll have a shared database, such as geographic information or lists or musical artists, that I want multiple applications to have access to. But only read access, they can’t change it. So root is out. But they also have their own database (in MySQL) or schema (in PostGreSQL) in which they will need to INSERT, UPDATE, DELETE and SELECT from tables. They may even need to CREATE tables, such as temp tables, depending on the situation. In these cases, such applications would require a dedicated user, because their are no commonalities that would allow this user account with its privileges to be reused by another application.

In this guide I’ll only deal with command line actions from MySQL client, not CPanel actions, but you’ll be able to transfer the concept to the different interface.

To create users you’ll most likely have to be root:

mysql -u root -p

Create the new user for this application:

CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

You’ll have to think about this


setting. If the only one accessing your database with this username is your application, and your MySQL process is on your local machine, then ‘localhost’ will do as a hostname. If you have a separate machine running MySQL as I do, then you’ll need to enter the hostname or IP address of your web server machine, as that is where the application will connect from.

CREATE USER 'username'@'webserver' IDENTIFIED BY 'password';

It is possible to use wildcards for the hostname, although you should think this through as generally it is a bad idea. If you are on an internal network and you have your web server cluster and SQL machine on the same subnet, you could do this:

CREATE USER 'username'@'10.20.30.%' IDENTIFIED BY 'password';

Which will allow 256 machines to connect to your MySQL server as that user. Think about the future use of your network before you do this.


Now you have a user who can’t do much. You’ll need to give them access to something. Since the user I’m creating will be the one populating the database with information and not just reading it, they will need greater privileges than the average web application that just reads and reports. The GRANT syntax can be found in the MySQL manual. My specific requirement allowed me to enter:


  • This GRANT is followed by a list of actions the user can take as seen in the manual page quoted above, (SELECT, DELETE, etc).
  • ON a certain database and tables – dbname.* means these privileges apply to ALL tables on the database called dbname.
  • TO username @ IP address – meaning if this user connects from another address they won’t receive these privileges

DON’T enter:
mysql> GRANT ALL ON *.* TO userguy@%

Which is the equivalent of giving them root privileges on all tables of all databases when connecting from anywhere. Pointless to try and manage users this way. And never grant *.* to a user anyway unless you aware of what all the * privileges are. Most people would be mortified to find out they used *.* and granted their web user the ability to DROP all their databases. Of course they don’t find out until after it has happened if they don’t know the user had that ability with *.* to start with.

Now, just to be clear everything is in effect, issue the



Keep in mind, that in newer versions, MYSQL will create a user when a GRANT is issued if the user doesn’t already exist. However, I prefer to explicitly CREATE a user, and keep track of it in a file.

Connecting remotely requires configuration settings changes. If you are not connecting locally, you’ll need to modify your MySQL’s my.cnf file to allow remote connections. This is part of your extended configuration and security, and I won’t discuss it in this article.

Now you can go about creating users and granting privileges with a total security plan in mind. Everyone can access only what they need, no one can access or change what they shouldn’t, and users can only connect from specific locations.

Validating POST values from HTML in PHP revisited

In a recent article titled “Submitting POSTed HTML Forms and Registering Variables in PHP” we examined a better way to treat tainted POST variables passed to your PHP script from an HTML form. We decided to create an array called $FORM_FIELDS that would contain only the names of the elements that we wanted in our new array thereby cleaning up all fields in one statement (loop) and omitting unwanted fields from becoming part of memory.

In a recent article titled “Submitting POSTed HTML Forms and Registering Variables in PHP” we examined a better way to treat tainted POST variables passed to your PHP script from an HTML form. We decided to create an array called $FORM_FIELDS that would contain only the names of the elements that we wanted in our new array thereby cleaning up all fields in one statement (loop) and omitting unwanted fields from becoming part of memory or registered session variables.

The creators of PHP went one further in this step by making the $FORM_FIELDS array a multi-dimensional array. They added a couple fields in this array to represent the data type and an additional function to process the data.

The array may look like this:

$FIELDS = array(
'country_id' => array('type' => 'int', 'function' => 'addslashes')

Now when you process your loop as done in the previous article you would just add a line for the datatype:

if (isset($FIELDS[$name]['type']))
$tmp[$name] = settype($FIELDS[$name], $FIELDS[$name]['type']);

The reason this is important is incoming values from an HTML form all seem to be of type ‘string’. If you are inserting this information into a database or otherwise performing calculations on it then it is nice to know it is of the correct data type that your functions expect.

But there’s more. If someone is attacking your program by sending POST values different from those you expect or different from those in your form then this will help.

You simply set the data type before you perform validation. If you have a field where a user selects the country name from a drop down list box and the value sent to your form is the country_id then you would pick ‘integer’ as the data type. But what happens when a user sends a string? If you try inserting it to a database you get an error. If you try to compare it against a list of known country_id’s for validation purposes you will also get an error.

If you perform the settype() function in PHP before your validation you will either set it to the correct datatype or get a value of zero. If a malicious user sent a string of ‘Aruba’ for your country_id field and you cast it to an integer you would get a zero. Anything other than an INT would become a zero. You can then proceed with your validation which would presume that a value of zero is not valid.

You will want to take a look at the PHP manual section called “Type Juggling.” There are certainly some caveats and you may not expect some conversions to be logical. For example, if you had the string “10thingsIhate” and you cast it to an INT you would have int(10).

Brush up on the PHP types. They can save you some work in your validation of HTML form input in your PHP scripts.