Prevent SQL injection when building query

Question

I know usually how to prevent it using preparedStatements, but now I have such a method for bulding queries. For example in Java:

private String buildQuery(String where) {
    String query = "SELECT id, name FROM someTable";
    if(where.length() > 0) {
        query = query + " WHERE " + where;
    }
    return query;
}

'where' string is like this 'variable = value'. How can i prevent it here? I thought of passing variable and value separately, creating prepared statement using them and then returning that prepared statement as string somehow, but I'm not sure.

Answer 1

This is not specific to any one DB API.

TL;DR: Don't pass "SQL fragments" around.

Rather than passing complete clauses fro a select statement, or (sub-)expressions to add into a select clause, pass the components keeping the user data separate from the identifiers.

In this case do not pass name = value, pass them separately. Then validate name is a valid column for the table, and generate a parameter for the value part.

Thus, pseudo-code (my Java is rusty):

function BuildCommand(string column, object value) {
  if !IsValidColumn("theTable", column)) throw InvalidOperation(...)

  string sql = "Select column from theTable where " + column + " = @p0";

  SqlCommand cmd = new SqlCommand(sql);
  cmd.Parameters.Add("@p0", value);

  return cmd;
}