| || |
Executiing Prepared Statements - Explain Please
I saw this from David Lai in a recent post. I did not wish to hijack the thread so i started a new one. The information from the other post is below for reference.
I read the Executing Prepared Statements link. I did not know what it was about and yet it seems important. Could someone explain this to me?
The 3rd thing any engineer learns in Database class is always used prepared statements in parameterized SQL queries.
Executing Prepared Statements
The problem with appending strings is it makes the query super vulnerable to SQL injection, and 2nd order SQL injection. It's best to let the framework handle all the escaping for you.
Coincidentally, it's a good way to tell if the Backend or DB Engineer has any formal DB training. You'll usually see a lot of issues with slow queries and sql injection in code that doesn't use prepared statements or ORM.
It's a simple concept,
Prepared statement - Wikipedia, the free encyclopedia
Basic idea is instead of putting parameters, you put '?' as a place holder.
stmt = prepareStmt("Select * from Users where username=? and password=?;");
Then to execute, you can do something like,
The advantages of doing this, is the parameters are automatically formatted and escaped for you. It reduces the chances of badly formatted queries or SQL injection.
Another benefit is performance. The query will get compiled and cached in the database, so when you run the same query repeatedly with different parameters, it executes it much faster. In high end databases like Oracle Enterprise Edition, the compiled query is also automatically optimized.
I have seen this done before, but I did not know that it had a name.