Home

Security around PostgreSQL

SQL Injection Exploits

When a valid SQL string is taken and used without proper escaping, it can be used to inject SQL commands into the database.

Handing injections with prepared statements

  • Option 1: Sanitize user-provided values to our app.
  • Option 2 (preferred): Rely on Postgres to sanitize values for us.

Postgres we essentially need to set a statement as a string and have values passed that are subbed in.

Preventing SQL Injection

Don't do this:

const { rows } = await pool.query(`
    SELECT * FROM users WHERE id = ${id};
`);

Do this:

const { rows } = await pool.query(
  `
    SELECT * FROM users WHERE id = $1;
`,
  [id]
);

Repository

https://github.com/okeeffed/developer-notes-nextjs/content/postgresql/SQL-And-PostgreSQL-The-Complete-Developers-Guide/35-Security-Around-PostgreSQL

Sections

SQL Injection Exploits
Handing injections with prepared statements
Preventing SQL Injection

Related

1-Simple-But-Powerful-SQL-Statements
10-Selecting-Distinct-Records
11-Utility-Operators-Keywords-And-Functions
13-PostgreSQL-Complex-Datatypes
14-Database-Side-Validation-And-Constraints
15-Database-Structure-Design-Patterns
16-Building-A-Like-System
17-Building-A-Mention-System
2-Filtering-Records
22-Understanding-The-Internals-Of-PostgreSQL
23-Indexes-For-Performance
24-Basic-Query-Tuning
25-Advanced-Query-Tuning
26-Common-Table-Expressions
27-Recursive-Common-Table-Expressions
28-Simplifying-Queries-With-Views
29-Optimizing-Queries-With-Materialized-Views
3-Working-With-Tables
30-Handling-Concurrency-And-Reversibility-With-Transactions
31-Managing-Database-Design-With-Schema-Migrations
32-Schema-Vs-Data-Migrations
34-Data-Access-Patterns-Repositories
35-Security-Around-PostgreSQL
36-Fast-Parallel-Testing
4-Relating-Records-With-Joins
5-Aggregation-Of-Records
6-Working-With-Large-Datasets
7-Sorting-Records
8-Unions-And-Intersections-With-Sets
9-Assembling-Queries-With-Subqueries
SQL-And-PostgreSQL-The-Complete-Developers-Guide