I have been reading in the manual about prepared statements and still do not quite understand when they should be used. Which of the following queries are better and why?
[code]userText = event.source.parent.getComponent(“TextArea”).text
userName = fpmi.security.getUsername()
fpmi.db.runPrepStmt(“INSERT INTO Comments (Name, UserComment) VALUES (?,?)”, [userName, userText])
fpmi.db.runUpdateQuery(“INSERT INTO Comments (Name, UserComment) VALUES (%s, %s)”, [userName , userText ])[/code]
The first one is better, because it protects against a problem known as a sql injection attack.
The problem is that in the second method, you’re inserting user-entered text directly into the query. The problem with that is that the text that the user entered in might contain things like a single quote, which would break the syntax of the query (at best) or at worst, could be used by an attacker (unlikely in the types of situations in which FactoryPMI is deployed) to compromise your database.
Yea, I was reading something like that in the manual. Let me ask this then… How does entering in a single quote in a string get prevented using the prepared statement? Is there some other code (part of the prepared statement function) that prevents this? What happens in this situation?
prepared statements are sent to the database in a totally different manner compared to standard SQL statements. They don’t rely on text-based demarcation to determine the boundaries of a value compared to the logic in the statement itselfy. Your examples had a slight typo, that when fixed might be illustrative. The correct comparison is:
[code]userText = event.source.parent.getComponent(“TextArea”).text
userName = fpmi.security.getUsername()
fpmi.db.runPrepStmt(“INSERT INTO Comments (Name, UserComment) VALUES (?,?)”, [userName, userText])
fpmi.db.runUpdateQuery(“INSERT INTO Comments (Name, UserComment) VALUES (’%s’, ‘%s’)” % (userName , userText ))[/code]
Notice that you need single quotes around the %s. In the second example, Jython is evaluating the string replacement BEFORE the string is sent to the database, so the query is just one big string that gets parsed. In the prepared statement example, a different API call is made to the database that sends the query (with question marks) and the values separately.
Hope this clears things up.
How do you protect from a SQL injection attack when using a SELECT statement? runPrepStatement does not work with a SELECT statement such as:
“SELECT location FROM tblLocation WHERE RoomNum=?”,[some.component.property]
Yeah, prepared selects would be the best way. In the meantime - you need to escape (sanitize) user input. Check out the SQL escape goodie
broken link