🛡️ SQL Injection Protection in Dynamic Geospatial Applications with PostgreSQL and OpenLayers

🧠 Introduction

When building powerful map-based applications that let users query and explore geospatial data—especially from dynamic data sources like PostGIS in PostgreSQL—security must be a first-class citizen. One of the most common and dangerous threats is SQL injection.

This article covers:

• Why SQL injection is risky

• How to guard against it

• Real-world constraints (e.g., query builders, dynamic tables)

• Best practices for geospatial applications using tools like OpenLayers, Node.js, and PostgreSQL

---

🔥 What Is SQL Injection?

SQL Injection occurs when untrusted user input is directly inserted into an SQL query. This allows attackers to manipulate queries to:

• Access unauthorized data

• Corrupt or delete tables

• Execute admin-level database commands

---

🧱 The Standard Defense: Parameterized Queries

The primary defense is always using parameterized queries:

✅ Safe Example (Node.js with pg):

const res = await pool.query(
  'SELECT * FROM parcels WHERE id = $1',
  [userInput]
);

❌ Unsafe Example:

const res = await pool.query(
  `SELECT * FROM parcels WHERE id = '${userInput}'`
);

---

⚠️ Real-World Constraints in Dynamic GIS Apps

When building something like a query builder for geospatial data, you often let users:

• Choose from many tables or files

• Pick columns and filters

• Generate dynamic queries from frontend → backend

This creates challenges where parameterized queries aren’t always usable — e.g., you can't parameterize table names or column names in most SQL engines.

---

🛡️ Safe Ways to Handle Dynamic Queries

1. ✅ Validate and Whitelist Dynamic Inputs

If users can select a table, column, or geometry field:

• Validate them against allowed values

• Never directly interpolate unchecked strings into queries

Example:

const allowedTables = ['roads', 'buildings', 'rivers'];
if (!allowedTables.includes(userTable)) throw new Error('Invalid table');

---

2. ✅ Use Parameterization Wherever Possible

You can still use $1, $2, etc. for values in WHERE conditions, even if other parts of the query are dynamic.

const query = `
  SELECT * FROM ${validatedTable}
  WHERE geom && ST_MakeEnvelope($1, $2, $3, $4, 4326)
`;
const res = await pool.query(query, [minX, minY, maxX, maxY]);

---

3. ✅ Read-Only Users for Database Access

Create a PostgreSQL read-only user with access limited to SELECT queries:

CREATE ROLE readuser LOGIN PASSWORD 'securepassword';
GRANT CONNECT ON DATABASE gis TO readuser;
GRANT USAGE ON SCHEMA public TO readuser;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readuser;

This ensures that even if an injection does happen, attackers cannot DROP or INSERT.

---

4. ✅ Tools to Help Detect SQL Injection Risk

You can use static code analysis tools to detect SQL injection risks:

• 🔍 ESLint plugin: eslint-plugin-security

• ✅ SQL linters and query builders like:

  • knex.js (safer dynamic SQL)

  • pg-format (for safe identifier formatting)

---

🛰️ Special Case: Geospatial Query Builders

In GIS apps, especially with OpenLayers + PostGIS:

• Users may pick layers from hundreds of geospatial tables

• You dynamically build SELECT queries and WMS layers

• BBOX queries are common

So you may do things like:

const tableName = sanitize(userInput);
const { rows } = await pool.query(`
  SELECT * FROM ${tableName}
  WHERE ST_Intersects(geom, ST_MakeEnvelope($1, $2, $3, $4, 4326))
`, [minX, minY, maxX, maxY]);

Ensure tableName and geom are whitelisted or validated.

---

🛠️ Practical Example: Get Primary Key Safely

When you’re dynamically building a feature table and need the primary key of a selected table:

const { rows } = await pool.query(`
  SELECT a.attname AS primary_key
  FROM pg_index i
  JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
  WHERE i.indrelid = $1::regclass
    AND i.indisprimary
`, [tableName]);

Safe and parameterized—even with a dynamic table name.

---

🧠 Summary: Best Practices for SQL Injection in GIS Apps

Practice	Why It Matters
Always use parameterized values	Prevents most injection
Whitelist dynamic identifiers	Prevents malicious table/column access
Use read-only DB users	Limits impact of attacks
Escape or sanitize identifiers	When interpolation is unavoidable
Use tools like pg-format	For safe string-based query building
Test with static analyzers	Detect vulnerabilities early

---

✅ Conclusion

Building dynamic query builders for geospatial data is powerful—but brings increased SQL injection risk. With proper parameterization, validation, and role-based DB access, you can build flexible, secure tools.

Security and flexibility can coexist. You just need to architect it thoughtfully.