‘SELECT *’ is Lazy—Write Safer Dynamic Queries in SQL Using Parameters
Posted inSQL

‘SELECT *’ is Lazy—Write Safer Dynamic Queries in SQL Using Parameters

‘SELECT *’ is Lazy—Write Safer Dynamic Queries in SQL Using Parameters

 

One of the most common mistakes developers make when working with SQL is using SELECT * indiscriminately and building SQL queries by concatenating strings. This approach isn’t just inefficient—it can be dangerous. In this post, we’ll explore why these habits are problematic and how parameterized queries can help you write more secure, efficient, and maintainable SQL.

1. The Problem with ‘SELECT *’ and String Concatenation

Using SELECT * might feel convenient during development, but it has real downsides in production:

  • Performance: Fetching every column wastes memory and bandwidth, especially if you’re only using a few fields.
  • Schema coupling: Your application becomes tightly coupled to the database schema. Adding or removing columns can break your app.
  • Security: Dynamically building queries using string concatenation opens the door to SQL injection attacks.

Example of an insecure query in JavaScript using string concatenation:

const userId = req.query.userId;
const query = `SELECT * FROM users WHERE id = ${userId}`;
db.query(query); // ❌ Vulnerable to SQL injection

If a malicious user supplies 1; DROP TABLE users; as their userId, you’ve just dropped your user table. Yikes.

2. Use Parameterized Queries to Prevent SQL Injection

Parameterized queries separate SQL code from data. This makes it impossible to inject malicious SQL through user input because the database treats inputs strictly as data, not executable code.

Here’s how to do it in some popular languages:

JavaScript (Node.js + mysql2):

const userId = req.query.userId;
const [rows] = await db.execute(
  "SELECT id, name, email FROM users WHERE id = ?",
  [userId]
);

Python (sqlite3 or psycopg2):

cursor.execute("SELECT id, name, email FROM users WHERE id = %s", (user_id,))

C# (ADO.NET):

var command = new SqlCommand("SELECT id, name, email FROM users WHERE id = @UserId", conn);
command.Parameters.AddWithValue("@UserId", userId);

Notice how each query includes placeholders (? or @UserId) along with a list or mapping of parameters. These mechanisms ensure your data is escaped and handled properly by the database engine.

3. Building Dynamic SQL Safely: WHERE Clusters

Oftentimes you need to build queries based on optional filters. The key here is not to manually construct the SQL string, but to build the conditions separately and then use parameter bindings.

Example in JavaScript using a dynamic filter:

let sql = "SELECT id, name FROM users WHERE 1=1";
let params = [];

if (req.query.name) {
  sql += " AND name = ?";
  params.push(req.query.name);
}

if (req.query.email) {
  sql += " AND email = ?";
  params.push(req.query.email);
}

const [rows] = await db.execute(sql, params);

This approach lets you add optional filters without making your app exploitable. The query is readable, maintainable, and safe from injection attacks.

4. Avoiding ‘SELECT *’: Explicit Columns Improve Performance

Even when your query is safe, using SELECT * remains inefficient. It’s best to be explicit with the columns you need, which lets the database optimize better, reduces memory usage, and makes your codebase easier to understand.

Instead of this:

SELECT * FROM users WHERE id = ?

Do this:

SELECT id, name, email FROM users WHERE id = ?

This practice becomes especially important at scale, where even small inefficiencies compound. It also avoids issues when your schema changes (e.g., adding large JSON fields that you don’t need in every query).

5. Using Query Builders and ORM Tools

If you’re dealing with complex query logic, using an Object-Relational Mapper (ORM) or a query builder can simplify things while still using safe, parameterized underpinnings.

Example: Knex.js (JavaScript)

const query = knex('users')
  .select('id', 'name')
  .where(builder => {
    if (req.query.name) builder.where('name', req.query.name);
    if (req.query.active) builder.where('is_active', true);
  });

const users = await query;

Knex uses parameter bindings and composes optimized SQL under the hood. Most ORMs—like Sequelize, TypeORM, SQLAlchemy, and Entity Framework—follow similar practices. But even if you’re not using an ORM, you can follow the pattern yourself.

6. Bonus: Logging and Auditing SQL Safely

When working with parameterized queries, debugging can be tricky because logs often exclude actual values. Instead of logging raw queries with values interpolated (which may reintroduce security concerns), format logs for developers only—not in production—or log query structure and parameters separately.

console.log("SQL:", sql);
console.log("Params:", params);

This still keeps things safe and gives your logs context without introducing vulnerabilities.

Conclusion

Writing SQL safely is not just about avoiding mistakes—it’s about building habits that scale with your app and protect your data. Avoid SELECT *, always use parameterized queries, and invest in smart query composition. These practices aren’t just best-practices—they’re security assets you can’t afford to ignore.

 

Useful links: