Handy SQL queries

Finding filetypes stored in a MySQL table

If your system allows fileuploads, and you have a database table where you store all the fileuploads, it could be interesting to see what types and how many types of files are being uploaded to your system. This query will find all filetypes uploaded to the system.

Replace the following values:

  • field_for_filename – The field name containing the filename.
  • table_name – The table containing the data
SELECT substring_index(lower(field_for_filename), '.', -1) as "Filetype",
count(substring_index(lower(field_for_filename), '.', -1)) as "Number of files"
FROM table_name
GROUP BY substring_index(lower(field_for_filename), '.', -1) ;

Replacing all of the selected values with your chosen value

If you for some reason need to replace some text, even inline text, with another this query can help you with that. For example if you need to update an IP address in a table, this query would solve that for you.

All the query basically does is that it looks for old_value text inside field_names values. If it finds old_value in a row it will update update that row with the old_value replaced with new_value. All other text within the field_name will stay the same except for the old_value turning into the new_value.

Replace the following value:

  • field_name– The field name containing the data you want to replace.
  • table_name – The table containing the data
  • old_value – The value you want replaced
  • new_value – The value you want ti replace old_value with
UPDATE table_name SET field_name = replace(field_name, 'old_value', 'new_value') WHERE field_name = '%old_value%'

You could most likely drop the WHERE clause and this update would still function as intended.

Chris Dale

I'm Chris Dale from Norway, founder and principal consultant at River Security ( Along with my security expertise, I have a background from system development and application management. Having a vast and broad experience in IT certainly help a great deal when working penetration tests and incidents.

I am an open, sharing and engaging person to be around, some even think I'm funny. I am usually enthusiastic and motivating when I work, and usually positive and optimistic about the general problems I encounter. I am passionate about security, both IT and physical security, which is one of the reasons I do a lot of public speaking at different events such as classes, conferences and workshops.

Driven by mottos such as "Magic is just science we don't understand yet" and "Think bad, do good", I attack today's security challenges with eagerness and enthusiasm. I consider myself a pragmatic person, with the ability to think outside the box, keeping the business in focus.

I also teach for SANS. My primary class I am teaching is Hacking Techniques, Exploits & Incident Handling. This course prepares you for the GIAC Certification in Incident Handling (GCIH). I find it extremely motivating and fun to teach others the art of security and hacking, and I often find that my passion and enthusiasm rubs off on my students.

Leave a Reply

Your email address will not be published.

Back To Top