DEV Community: BC-TE-CH

Common SQL Injection Attacks and How to Prevent Them (Option 2)

BC-TE-CH — Mon, 28 Sep 2020 00:03:57 +0000

Method 1: Stacked Queries

This method involves completing one SQL statement and then writing a completely new one. For example, a website has users input a username to view information, and the SQL statement is, "SELECT * FROM Users WHERE Username='username'". If stacked queries are supported, then a user can input "'; SELECT * FROM *;" and get the contents of the entire database when SQL runs "SELECT * FROM Users WHERE Username=''; SELECT * FROM *;".

To prevent this, do not allow stacked queries if you have the option. For instance, when using mysql and php, use the statement "mysqli_query" instead of "mysqli_multi_query". The former will stop stacked queries while the latter permits them.

Method 2: Error Based Injection

This method makes use of SQL error messages that are displayed to the user. The code is somewhat complicated, but the concept is simple. A user attempts to do something that will almost certainly create an error, such as converting a table name to an integer, and then gains information from the resulting error message. Once a user knows the names of various tables, they will have a much greater ability to compromise the database.

To stop this attack, make sure that internal SQL errors are never displayed to a user. SQL errors should be handled before they make it to the user to stop users from gaining information from them.

Method 3: Boolean Based Injection

In this attack, a user appends various conditions to a conditional statement. In the example of searching a users list with the SQL code "SELECT * FROM Users WHERE Username='username'", username could be set to "myusername' AND conditional statement". If your user information is returned, then the conditional statement is true. If it isn't, then the statement is false. By performing a series of these queries, an attacking can gather a variety of information about the database.

To prevent this attack (and most SQL attacks), user input should never be run as SQL code. There are many ways to do this, with varying levels of potential vulnerability, but the safest method is to use prepared statements. This passes user input to SQL as a parameter to a prewritten statement. Doing this ensures that SQL will not interpret what the user entered as SQL code. It will be treated as whatever data type it is meant to be.

A Video Demonstrating an Injection Attempt

Thoughts on the Aura App

BC-TE-CH — Mon, 14 Sep 2020 00:54:48 +0000

The Aura App was made mandatory by Albion College for all students to install. It tracks a student's location at all times and records the data. There are obvious privacy concerns with an app like this because it is impossible to know what data the college is looking at. They say that they will only access it when a student is found to have Covid or when a student leaves campus, but there is no way to verify that.

Is it worth it?

The natural question to ask when an app like this is created is are the costs worth the benefits. People often have strong opinions on topics about security vs. privacy, but there are legitimate pros and cons to an app like Aura. The benefit of this app is that it provides better information about who has come into contact with who when someone is found to be infected. This will likely help limit the spread of Covid-19, but it is hard to say how much. The downside of this app is that students have no way of knowing what the college is doing with their data and storing all of that data makes it an attractive target. I don't think we will ever come to a balance between security and privacy that everyone agrees with, but we should try to define what our limits are.

My Thoughts

Personally, I don't think the benefits of this app are worth the costs. I don't think a college should track it's students at all times even if they are doing it for good reasons. There are too many risks to privacy and there are also risks to security. If the wrong people get access to this location data, they could do a lot of harm. They could see when people go certain places and potentially attack someone when they know they will be isolated. Multiple security flaws have already been discovered with this app, some of them completely negligent. Leaving in hard coded login credentials is completely inexcusable in an app that stores data this private, and the school should have stopped using the app when they found this out. Instead the company that made it just patched it out, but there is no telling how many other security flaws there are. The "service" also has another flaw where someone can go to a hidden part of the Aura website and get users' personal information by simply incrementing the account number. I don't know how Albion College can justify using this app now that these horrific security flaws have been found.

A Better Way

I think a better solution would be to have students personally track people who they have been in close contact with. For classes, students would just need to sign in and it would be easy to tell who has been in contact with who. This system would obviously be worse at identifying who people have been in contact with, but students retain control of their own data and don't have to put blind faith in the college. It would be harder to deal with people leaving campus. If the college feels that they absolutely need to know if someone has left campus, then a solution that is at least better is to track students' locations but don't store that data. This way malicious people can't access weeks of people's location history.

Entity-Relationship Diagram for a Covid-19 Risk Dashboard

BC-TE-CH — Mon, 07 Sep 2020 00:01:57 +0000

I was tasked with creating an ER diagram for a theoretical Covid-19 risk dashboard. This is the diagram I created:

And here is a spreadsheet with tuples (a tuple is one row/record/entry):

Entities

The entities I chose are patient, occupation, behavior, location, hospital, and risk.

Patient

Patient describes the user and has the primary key of User ID. Primary keys are unique fields that identify each tuple of an entity. Patient also contains foreign keys for occupation and location to create a relationship between the entities. A foreign key is when an entity contains the primary key of another entity.

Occupation

Occupation describes the patient's job and contains attributes that are relevant to the risk of contracting Covid-19. The primary key is a combination of company name and job title because the same job within the same company should be similar enough to not need individual entries.

Behavior

Behavior describes how a patient behaves in their everyday life. Its primary key is also User ID because behavior will be unique to each user. It also contains a foreign key for occupation to reflect the relationship between occupation and behavior. Its relationship with patient is maintained through the shared primary key.

Risk

Risk describes a patient's risk in general and from Covid-19. It also uses the primary key of User ID because it is unique to each user and is based on all of the other information. The User ID field links Risk to Patient.

Location

Location describes the characteristics of a Patient's physical environment as well as relevant government policies. Its primary key is City/Town because any policies within the same city or town should be same.

Hospital

Hospital describes the characteristics of a hospital within a location. The foreign key for Location is used to form this link. Hospital has the primary key of name because hospital names tend to be very location specific.

Why This Layout?

I chose this layout in an attempt to maximize user convenience. The entities that use User ID as a primary key will be unique to each user, so the user will have to enter information manually for the patient and behavior entities(Risk would be generated by the dashboard). The other entities, however, could be shared by multiple users(patients), so if there is already an entry for an occupation, location, or hospital then the user will not have to enter in all of the information. Hospital's link to location also means that a list of known hospitals in that location can be generated after the user enters their location.

A Video Discussing the Same Topic:

Who Am I? (And SQL vs NoSQL)

BC-TE-CH — Sun, 30 Aug 2020 23:40:00 +0000

My Background

I'm a student at Penn State University in the college of IST. I'm currently studying cyber security, but I'm also interested in app/web development. I don't have very much experience other than some general programming classes, but now I'm starting to branch out into more specific areas. Most of the experience that I do have is in java, but I have done some work with python. I also worked with a small team to build an app in android studio which gave me exposure to an IDE that is used to make tons of mobile apps and to collaboration tools like GitHub.

Why Cyber Security

I chose to study cyber security because I want to help prevent data breaches and protect people's information. Cyber security is also particularly interesting because you get experience from both sides of the fight. You can try to break into systems to find weaknesses and then you use that information to try to stop other people from breaking into the system. It also is necessary in nearly every industry because of how important companies' digital assets are. Everyone from NASA to Pepsi needs someone who knows cyber security which gives a lot of options for what industry to work in.

Job Goals

I'm not sure exactly what kind of job I want after I graduate. Penetration testing seems interesting, but I won't really know until I have more experience in that area. Maybe I'll try to combine my interests by doing security as part of an application development team. Mostly though I just need to try more things before I decide what area I want to ultimately go into. Regardless of what specific area I got into, I still want to help protect people's information and finances.

Personal Interests in Tech

App Development

I would like to develop some small applications that save people time. If an app saves someone even just 10 seconds a day, then eventually it starts to add up. Applications that manage small annoying tasks for people or keep them organized have a lot of potential to provide a big return for a relatively small investment, and it's very satisfying to make things that are immediately useful. Also, even if no one ever uses it other than yourself it can still provide you enough benefit to have been worth the time.

Programming competitions

I have participated in a few programming competitions and I always find them enjoyable. During my time in college, I will definitely seek out more to participate in to gain experience and just to have fun. They provide a good way to work with a team towards a very tangible goal, and it's very satisfying to finally get a program working. Another added benefit is that when a company runs the competition, there is often an opportunity to get an internship if you do well. Internships obviously have the possibility of turning into a job, so it can be a very worthwhile thing to do with your time.

SQL vs NoSQL Video

As promised SQL vs NoSQL:

and the link: https://www.youtube.com/watch?v=K8iyulkY3Fo&feature=youtu.be

Thanks for reading and there are more posts to come.