Injection can happen on any SQL statement not run properly. For example, let’s pretend your comment table has two fields, an integer ID and the comment string. So you’d INSERT as follows: INSERT INTO COMMENTS VALUES(122,’I like this website’); Consider someone entering the following comment: ‘); DELETE FROM users; — If you just put the … Read more
Stored Procedures and/or prepared statements: https://stackoverflow.com/questions/1973/what-is-the-best-way-to-avoid-sql-injection-attacks Can I protect against SQL Injection by escaping single-quote and surrounding user input with single-quotes? Catching SQL Injection and other Malicious Web Requests With Access DB, you can still do it, but if you’re already worried about SQL Injection, I think you need to get off Access anyway. Here’s … Read more
It most certainly does. This example is straight from the Spring 3.0 docs (but is the same in 2.*): String lastName = this.jdbcTemplate.queryForObject( “select last_name from t_actor where id = ?”, String.class, 1212L); As you can see, it strongly favors prepared statements (which it must be using behind the scenes for you): you specify the … Read more
A parameterized query doesn’t actually do string replacement. If you use string substitution, then the SQL engine actually sees a query that looks like SELECT * FROM mytable WHERE user=”wayne” If you use a ? parameter, then the SQL engine sees a query that looks like SELECT * FROM mytable WHERE user=<some value> Which means … Read more
Using string concatenation for constructing your query from arbitrary input will not make PreparedStatement safe. Take a look at this example: preparedStatement = “SELECT * FROM users WHERE name=”” + userName + “”;”; If somebody puts ‘ or ‘1’=’1 as userName, your PreparedStatement will be vulnerable to SQL injection, since that query will be executed … Read more
I’d say for your particular, and probably canonical, example for parametrized queries, yes it is sufficient. However, people sometimes write code like this cmd.CommandText = string.Format(“SELECT * FROM {0} WHERE col = @col;”, tableName); cmd.Parameters.Add(“@col”, …); because there is simply no way to pass the tablename itself as a parameter and the desire to do … Read more
CodeIgniter’s Active Record methods automatically escape queries for you, to prevent sql injection. $this->db->select(‘*’)->from(‘tablename’)->where(‘var’, $val1); $this->db->get(); or $this->db->insert(‘tablename’, array(‘var1’=>$val1, ‘var2’=>$val2)); If you don’t want to use Active Records, you can use query bindings to prevent against injection. $sql=”SELECT * FROM tablename WHERE var = ?”; $this->db->query($sql, array($val1)); Or for inserting you can use the insert_string() … Read more