Introduction
I have demonstrated some data manipulations with ORM(Object-Relational Mapping) frameworks such as TypeORM so far. Today's topic is still cases with TypeORM. However, it focuses on the security, which is my main field at the moment. I think you understand why I select topics regarding complex queries, performance, and the difference from the Microsoft ecosystem.
Demo Application
I developed a demo application in GitHub.
Demo Application for SQL injection tutorials
Utilization of ORM is Safe?
Some experienced developers mention that ORM(Object-Relational Mapping) frameworks such as TypeORM support prevent SQL injection, which is a traditional security matter. However, I still find high-risk cases with ORM frameworks in GitHub repositories, public project spaces, or in my work experience.
Example High-Risk Code with TypeORM
This is a typical high-risk case with TypeORM even though they use the ORM framework that prevents the SQL injection.
// TO-DO temporary solution
// I need to update it when I have a spare time
const users = await this.userRepository.query(
`SELECT user.id as id,
user.firstName as firstName,
user.lastName as lastName,
user.age as age,
user.clientId as clientId,
user.createdDateTime as createdDateTime,
user.updatedDateTime as updatedDateTime,
Client.companyName as 'clientCompanyName',
Client.createdDateTime as 'clientCreatedDateTime',
Client.updatedDateTime as 'clientUpdatedDateTime'
FROM User INNER JOIN Client ON user.clientId = Client.id
WHERE clientId = ${clientId}
AND (firstName LIKE '%${name}%'
OR lastName LIKE '%${name}%')`
);
Why ORM is Safe?
Let's step back once. We should review the traditional way of executing SQL.
const users = await connection.query(
`SELECT *
FROM users
WHERE clientId = ${clientId}
AND name LIKE %${name}%;`);
await connection.end();
How do you think if we enter %' OR 1=1 -- in the form to search users?
The method accepts the value, and the generated SQL is:
SELECT *
FROM users
WHERE clientId = 1
AND name LIKE '%%' OR 1=1 -- %;
The application should return only users of a client ID, 1, but this actually returns all users including the other clients'.
On the other hand, most developers basically utilize ORM frameworks in the modern age. Below is the case with TypeORM.
await this.userRepository.find({
where: [
{ clientId: clientId,
name: Like(`%${name}%`) },
],
});
The code generates the same SQL code as
SELECT * FROM users WHERE clientId = 1 AND name LIKE '%{{name}}%';. However, ORM frameworks treat the value, name as prepared statements, not partial SQL syntaxes. Of course, the return value is empty.
POINT: Prepared Statements
The certain values are left unspecified when the SQL is sent and compiled. The application binds the values to the parameters, and the database executes the statement.
This is Serious Vulnerability?
Some people do not think this is a critical issue. They might say:
Hey Yoshi! Do not worry too much. The application just displays user's information which does not include their privacy, such as their password. Take it easy!
UNION-based SQL Injection
If an attacker notices that the system has a security hole here, they will enter %' OR 1=1 UNION SELECT 'fakeName' as companyName, email as firstName, password as lastName FROM Login; -- to get login details. "First Name" column has user's login email addresses and "Last Name" column has their password.
POINT: External Authentication
Some enterprise applications utilize external authentication provided by Firebase Auth and Auth0. However, I also know that some companies still manage legacy applications with old-fashioned frameworks, which are not supported by them.POINT: Password Encryption
The sample app stores the password as plain text, which is not encrypted, and you might think that you are okay because your app encrypts critical data, but do you adopt the latest encryption methodology?
This is Still Serious Vulnerability?
Some people still do not think that this is a critical issue, and they might say:
Hey Yoshi! Do not worry! Crackers have no idea about all table schemas including a login table. If not, they are not able to execute it. Take it easy, man!
For SQLite, they will enter %' OR 1=1 UNION SELECT 'fakeName' as companyName, sql as firstName, 'fakelastname' as lastName FROM sqlite_master; -- to show table schemas. "First Name" column has tables' information.
Solution
The first code should be replaced by the below code. Yes, this is simple.
const users = await this.userRepository.find({
where: [
{ clientId: clientId, firstName: Like(`%${name}%`) },
{ clientId: clientId, lastName: Like(`%${name}%`) },
],
relations: ["client"]
});
Causes of Vulnerability
Some C-level or managers assume that the cause is the lack of education for junior developers, and they attempt having the security training or pair-work with senior engineers.
Is this true?
The fundamental knowledge of secure coding is mandatory. However, to be honest with you, this might be one of the reasons and I have to conclude this is not correct. I deeply investigated and found some potential causes.
Lack of Third-party's Module Information
I moved from the Microsoft development world to OSS, which is the development in Node.js/TypeScript with third-party modules. I think that their official documents provide too general and basic information only in comparison with Microsoft.
They basically provide the cases with detailed examples and combination of libraries. It is easy because most libraries are provided by Microsoft themselves!
For instance, with TypeORM, I found the below code that developers use the official ways for simple code. However, if they have to write complex data operations such as creating join tables or executing with subqueries, they copy and paste the raw SQL and leave it once.
import { Service, Token } from "typedi";
import { Repository, UpdateResult } from "typeorm";
import { InjectRepository } from "typeorm-typedi-extensions";
import { Client } from "../entity/Client";
import { User } from "../entity/User";
export interface IRiskyUserUsecase {
getAll(clientId: number): Promise<User[]>;
save(user: User): Promise<User>;
update(id: number, user: User): Promise<UpdateResult>;
remove(user: User): Promise<User>;
search(clientId: typeof Client.prototype.id, name: string): Promise<User[]>;
}
export const RiskyUserUsecaseToken = new Token<IRiskyUserUsecase>();
@Service(RiskyUserUsecaseToken)
export class RiskyUserUsecase implements IRiskyUserUsecase {
@InjectRepository(User)
private userRepository: Repository<User>;
async getAll(clientId: number) {
return await this.userRepository.find({ clientId: clientId });
}
async save(user: User) {
return await this.userRepository.save(user);
}
async update(id: number, user: User) {
return await this.userRepository.update(id, user);
}
async remove(user: User) {
return await this.userRepository.remove(user);
}
// TO-DO temporary solution
// I need to update it when I have a spare time
async search(clientId: typeof Client.prototype.id, name: string) {
const users = await this.userRepository.query(
`SELECT user.id as id,
user.firstName as firstName,
user.lastName as lastName,
user.age as age,
user.clientId as clientId,
user.createdDateTime as createdDateTime,
user.updatedDateTime as updatedDateTime,
Client.companyName as 'clientCompanyName',
Client.createdDateTime as 'clientCreatedDateTime',
Client.updatedDateTime as 'clientUpdatedDateTime'
FROM User INNER JOIN Client ON user.clientId = Client.id
WHERE clientId = ${clientId} AND (firstName like '%${name}%' OR lastName like '%${name}%')`
);
return users.map(
(u): User => {
return {
id: u.id,
firstName: u.firstName,
lastName: u.lastName,
age: u.age,
clientId: u.clientId,
client: {
id: u.clientId,
companyName: u.clientCompanyName,
createdDateTime: u.clientCreatedDateTime,
updatedDateTime: u.clientUpdatedDateTime,
},
createdDateTime: u.createdDateTime,
updatedDateTime: u.updatedDateTime,
};
}
);
}
}
It would be best if you maximize the full benefits of TypeORM and do not execute the raw SQL. However, at least, you should use the prepared statements for security reasons like below if you need.
--------
@Service(RiskyUserUsecaseToken)
export class RiskyUserUsecase implements IRiskyUserUsecase {
@InjectRepository(User)
private userRepository: Repository<User>;
// TO-DO temporary solution
// I need to update it when I have a spare time
async search(clientId: typeof Client.prototype.id, name: string) {
const users = await this.userRepository.query(
`SELECT user.id as id,
user.firstName as firstName,
user.lastName as lastName,
user.age as age,
user.clientId as clientId,
user.createdDateTime as createdDateTime,
user.updatedDateTime as updatedDateTime,
Client.companyName as 'clientCompanyName',
Client.createdDateTime as 'clientCreatedDateTime',
Client.updatedDateTime as 'clientUpdatedDateTime'
FROM User INNER JOIN Client ON user.clientId = Client.id
WHERE clientId = :clientId AND (firstName like '%:name%' OR lastName like '%:name%')`,
[{ clientId: clientId }, { name: name }]
);
return users.map(
(u): User => {
return {
id: u.id,
firstName: u.firstName,
lastName: u.lastName,
age: u.age,
clientId: u.clientId,
client: {
id: u.clientId,
companyName: u.clientCompanyName,
createdDateTime: u.clientCreatedDateTime,
updatedDateTime: u.clientUpdatedDateTime,
},
createdDateTime: u.createdDateTime,
updatedDateTime: u.updatedDateTime,
};
}
);
}
}
But again, you should not do that for code-reviewers. Can you recognize the difference between the raw SQL with and without prepared statements?
It must be tough!
Upgrade of Old-Fashioned Applications
We sometimes develop pure new applications = we can select the appropriate frameworks and environments, which are the modern architecture.
On the other hand, we have to convert from the old applications to ones with the modern frameworks. For example:
- Conversion from Web Form applications in .Net to MVC App/SPA in .Net Core
- Conversion from MVC applications in PHP to React applications in Node.js/TypeScript
In this case, it might occur the above case because developers try completing the task within limited time allocation = they use existing code.
Error of Human Resource/Time Allocation
I assume that this is the main reason. Developers handle their tasks with limited human/time resources every day, even though it is sometimes unreasonable and puts pressure on them. It is fantastic if they can always choose the best solution, but it is difficult.
Managers and leads, do you think that you understand the current situation and manage it properly?
It is unacceptable for us to fire developers making security mistakes even though nobody wants to write insecure code, and it often happens due to external factors. Without thinking about why they made such a choice, it would not be the ultimate solution.
Potential Solutions for Developers
- Review the parts of comment out with TO-DO and temporary solutions once - Developers and code-reviewers might not imagine how simple mistakes pose critical incidents. It is good timing to review them.
- Security issues would come from human errors - Please do not focus on improving their security skills/knowledge. This is significant but you would also need to review your team management and development environment.
Potential Solutions by Publishers
- Provide intermediate/senior-level information and a variety of examples as well - If the information is too general, developers might select lazy ways even though they do not want to.
- Provide information regarding the utilization of the modern modules/library's combination - I faced multiple times that when I used the third-party module independently, it worked well. But when I used it with the other modules, it did not work. If developers have trouble implementing complex tasks with multiple third-party modules, they might select temporary solutions.
Demo Application
I developed a demo application in GitHub.
Demo Application for SQL injection tutorials
Conclusion
I will continuously publish security articles. However, I also understand the importance of providing the technical skills/knowledge in a non-security field. I believe that if we can provide helpful information about the development itself or project management, it would indirectly prevent security matters.
We are with you, friends.
Bio
When I was 30 years old, I went to Australia for changing my career on a Working Holiday visa. I graduated from University of Sydney with a Bachelor of Computer Science and Technology. During the period, I also worked as a Fraud Prevention Analyst at Amex in Sydney, Australia (yes, it was super busy. I went to my uni during the day and worked at night...)
After graduation, I worked as a C#/.Net developer for an Australian small business for 5 years. Now, I came back to Japan and work as a TypeScript/Go/React developer for a Japanese security start-up company.
I love learning new fields which is a big challenge. I am happy if you support me to accelerate the improvement of my skill/knowledge. Please feel free to contact me if you are interested in my unique career.
Thanks,
Top comments (2)
Very interesting post, thank you !
Great post mate! Thanks!!