All ORMs build at least some of their SQL using string concatenation.
Prepared statements with bind variables only work when the SQL string is static and only the variables change - but ORMS are used to construct full SQL statements with custom select / while / join clauses etc.
Yes, all ORM's build their SQL using string concatenation, BUT the good ORM's won't use string concatenation on user data, instead they will use bind parameters.
This way the query sent to the server looks like this:
SELECT * FROM whatever WHERE email = :1 AND user_name = :2;
And then the parameters are passed to the database server separately to bind to the above placeholders.
This way the database server knows what is user provided data and what is part of the SQL, and no special quoting is required since the database server handles that internally. It's much safer in that SQL injection becomes impossible at that point.
No, you just have to use some intelligence in tracking your bind variables and bind values together instead of blindly bashing strings together, then at some distant location in the code guess & hope about the number of bind values used. I've got code that does it just fine.
No you can definitely use prepared statements with bind variables since you can dynamically name the bind variables. So if you have a statement where you don't know how many bind variables will go into the query you can give the bind variable a name and add a number at the end that you increment for each additional bind variable (e.g. @param + i for @param1, @param2, @param3, etc.).
Depending on how complex your query is it can get to be a bit of work but it's doable.
Comments
All ORMs build at least some of their SQL using string concatenation.
Prepared statements with bind variables only work when the SQL string is static and only the variables change - but ORMS are used to construct full SQL statements with custom select / while / join clauses etc.
Yes, all ORM's build their SQL using string concatenation, BUT the good ORM's won't use string concatenation on user data, instead they will use bind parameters.
This way the query sent to the server looks like this:
And then the parameters are passed to the database server separately to bind to the above placeholders.This way the database server knows what is user provided data and what is part of the SQL, and no special quoting is required since the database server handles that internally. It's much safer in that SQL injection becomes impossible at that point.
That's not what OP was saying. That SQL string you just provided is static. At some point the ORM has to assemble that string.
OP said:
This is wrong.I said "All ORMs build at least some of their SQL using string concatenation"
The "at least some" was meant to imply that they also use bind variables.
No, you just have to use some intelligence in tracking your bind variables and bind values together instead of blindly bashing strings together, then at some distant location in the code guess & hope about the number of bind values used. I've got code that does it just fine.
No you can definitely use prepared statements with bind variables since you can dynamically name the bind variables. So if you have a statement where you don't know how many bind variables will go into the query you can give the bind variable a name and add a number at the end that you increment for each additional bind variable (e.g. @param + i for @param1, @param2, @param3, etc.).
Depending on how complex your query is it can get to be a bit of work but it's doable.