Magento1 protection from MySQL injection | SQLSTATE[42000]: Syntax error or access violation

Magento is a very popular eCommerce platform which is used for hundreds of thousands of websites around the world. Many web stores are still using the Magento 1.7 version and usually they are projects with a large number of custom extensions that were developed to order.

Some web stores were being developed so fast that the first thought of owners was to do it quickly in order to provide their customers with a new feature and get ahead of competitors.

In most of the cases errors are not created intentionally, in the first place you want to create something new, something useful. Somebody will get a new opportunity from using your product, but others, unfortunately, may get an absolutely different opportunity - to take your data without asking.

One of the most common "opportunities" to get access to your data is an SQL injection. Below are types of attacks that may be applied to web applications:

1. Cross-Site Scripting (XSS)
2. SQL Injection (SQLi)
3. Path Traversal
4. Local File Inclusion (LFI)
5. Distributed Denial of Service (DDoS)
6. Server Side Includes (SSI)

On the internet, there is quite enough information about what SQLi is, but I would say briefly that this is more of a “hole” in the code of your website than a web browser’s vulnerability. If you use CDN most likely you are partially protected, also many hosting providers have some rules of blocking ‘“bad” queries (I would say that these rules may also block “good” queries, but you can always contact support in order to have them added to the exceptions).

What is dangerous about SQLi?

Everything of value that you have - is your customer’s data (it’s good if you don’t store credit cards information, but I recommend double-check it). It may be anything starting with a username or a telephone, an email up to your customers’ orders. In other words, it's almost like in the movie "I Know What You Did Last Summer".

An intruder can get access to your website management and in this case his imagination may have no limits:

• To add JS scripts to your website for credit cards screaming;
• To study the behavior of your customers in order to steal their data on other services.
Where is a website the most vulnerable?

The biggest vulnerability is there where it’s necessary to get data from a user and search by it in a database. Developers try to make code universal and this is also a vulnerability.

Let’s consider an example with Мagento - any version fits (but I think that any CMS works by this principle) - and product listing page + layer navigation (obligatorily). When a user chooses an attribute for filtering products Magento uses a special module to generate an SQL database query in order to find products that match filter criteria as much as possible. Imagine that you are in some category with over 1000 products and you are trying to filter them by color. The URL of this page will be approximately like this category_page.html?color=12 - in our case 12 is white color.

An SQL query below is very simple, but I wrote it just to explain how it works.

As you see the value of the color 12 was sent as-is and nothing prevents us from sending something else instead of 12, usually, it’s special symbols.

Below is a message from the log file when somebody tries to add other symbols to an SQL query "/catalog/product/?brand=173217'A=0" In addition to 173217, a special symbol and a string A=0 were sent.

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''A=0)

In other words, we don’t check an input value type, even though attribute types work very well in Magento.

Only attributes of select and multi-select types are allowed for filtering. pic.


The point is that an attribute value is always a number, not a set of letters, and you can apply a filter to all incoming queries - an attribute value can be a number and nothing else.

Also there are predefined sorting options are available:

• price: ASC/DESC
• name: ASC/DESC

And display modes:

• mode: list/grid

If you have special attributes with text values you can also predefine them.

Below is an example, where sorting by order can be only: 'name', 'price', 'most_viewed', 'customer_rating', 'expert_rating', other values will be ignored.

What we got

We implemented this improvement for one of our Magento 1.* customers and this is what we got:

• A number of SQLi attempts using filters (layered navigation) were reduced to 0, which is wonderful;
• In var/reports folder we don’t have thousands of files (error notifications like SQLSTATE[42000]: Syntax error or access violation) anymore.

Hackers don't sleep, the world is changing and we have a lot of work in the future.?

If your web store works on Magento 1.* - come to us and we will make your life calmer.