title: How to build dynamic query by binding parameters in node.js-sql?
body: <p>I'm using <code>nodejs-mysql</code> module to do query in node.js recently, and in my working case I could only use the parameter-binding syntax like:</p>
  
  <pre><code>SELECT * FROM table WHERE name = ?
  </code></pre>
  
  <p>Now I want to build dynamic sql with these <code>?</code> OR <code>??</code> parameters. Assume that I have 2 conditions(<code>name</code> and <code>age</code>) which either of them could be null (if user doesn't provide it),
  So I want to build MySQL in 3 cases:</p>
  
  <ol>
  <li>only <code>name=Bob</code>: <code>SELECT * FROM table WHERE name = 'Bob'</code></li>
  <li>only <code>age=40</code>: <code>SELECT * FROM table WHERE age &gt; 40</code></li>
  <li>both: <code>SELECT * FROM table WHERE name = 'Bob' AND age &gt; 40</code></li>
  </ol>
  
  <p>I know it's easy if you build the query on your own, but how can I achieve it when using placeholders which can only bind field or values ?</p>
  
  <p>In document of <code>nodejs-mysql</code>, placeholder <code>?</code> only stands for values and <code>??</code> stands for fields:</p>
  
  <ul>
  <li><a href="https://github.com/felixge/node-mysql/#escaping-query-values" rel="noreferrer">https://github.com/felixge/node-mysql/#escaping-query-values</a></li>
  <li><a href="https://github.com/felixge/node-mysql/#escaping-query-identifiers" rel="noreferrer">https://github.com/felixge/node-mysql/#escaping-query-identifiers</a></li>
  </ul>
  
  <p>My first thinking of solution is to insert query piece by using these placeholders, but it comes to failure because both <code>?</code> and <code>??</code> will escape my query piece, and my query will be executed incorrectly.</p>
  
  <p>My code so far is as below, which I'm defenitly sure it's not correct because query piece has been escaped:</p>
  
  <pre><code>// achieve paramters from url request
  var condition = {};
  if(params.name)condition["name"] = ["LIKE", "%" + params.name + "%"];
  if(params.age)condition["age"] = parseInt(params.age, 10);
  
  //build query
  var sqlPiece = buildQuery(condition);
  
  //try to replace ? with query
  var sql = 'SELECT * FROM table WHERE ?';
  
  connection.query(sql, sqlPiece, function(err, results) {
    // do things
  });
  
  // my own query build function to proceed conditions
  function buildQuery(condition) {
    var conditionArray = [];
    for(var field in condition){
      var con = condition[field];
      if(con !== undefined){
        field = arguments[1] ? arguments[1] + "." + field : field;
        var subCondition;
        if(con instanceof Array) {
          subCondition = field + " " + con[0] + " " + wrapString(con[1]);
        }else{
          subCondition = field + " = " + wrapString(con);
        }
        conditionArray.push(subCondition);
      }
    }
    return conditionArray.length &gt; 0 ? conditionArray.join(" AND ") : "1";
  }
  
  //wrap string value
  function wrapString(value){
    return typeof value === "string" ? "'" + value + "'" : value;
  }
  </code></pre>
  
  <p>So is there any way I can fix this problem?</p>
  
  <h2>Update</h2>
  
  <p>Thanks to Jordan's Offer, it's working, but :</p>
  
  <p>I know building query by string concat is very good, but in my case I can't use that, because I'm using some <strong>middleware</strong> or handle mysql and controller, so what I can do is to define <strong>interface</strong>, which is a sql string with placeholders. So, the interface string is predefined before, and I can't modify it during my controller function.</p>