2

I have a question regarding the value initialization

  • In query tool:

    Insert into tablename(field1, field2, field3..) 
    values (value1, value2, value3..);
    
  • In my JavaScript code, I am using:

    db.query({ text: 'INSERT INTO tablename(field1, field2, field3) 
        VALUES ($1, $2, $3)', values: [value 1 , value 2 , value 3'] }).then(...)
    
  • is this a good practice?

    select * from tablename where _id="+[value]+" AND field1 = "+[value]+";
    

I have listed 3 query above. As per my knowledge 1 & 2 are right as I tried them in many of my projects. Can someone clarify is the option 3 is also a correct method? Does it match the standards?

1

Number 1 is the basic "insert .. values" syntax and works well.

Of course, you should be using Parameterised Queries/ Prepared Statement to avoid SQL Injection Attacks and to simplify Data Type conversions.

Number 2 extends Number 1, inserting the values and returning those self-same values that were just inserted which is, in my opinion, a bit pointless, because you just supplied the values to be inserted and so you already know what they are.

Number 3, is simply treating the SQL as text and doing string replacements to get the parameter values in there. This is downright risky, leaving you exposed to SQL Injection attacks.

It's possible that you're using some package or other (you mention a "query tool") and, if so, that might "handle" this format and do the substitutions on your behalf. If that's the case then you can hope that you're in a better position but you're completely reliant on the suppliers of this package getting their code absolutely right and never forgetting that all-important control-character escaping.

Parameterised Queries/ Prepared Statements are definitely the recommended way to go.

Obligatory XKCD Reference: Exploits of a Mom

Regards, Phill W.

  • RETURNING * returns all the values (from all the columns), even the columns not included in the VALUES list (autogenerated serials, timestamps, anything with a default value/expression) – ypercubeᵀᴹ Sep 5 '18 at 11:34
4

If the values are obtained from a user input, 2) and 3) are a bad idea.

You shouldn't put user supplied values directly into the SQL string, but use a prepared statement with bind variables. Not doing that, makes your application vulnerable to SQL Injection

How you use a prepared statement with bind variables depends on the programming language you are using.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.