# Prepared Statements **URL:** \ **Category:** Archived Topics\ **Created:** [April 17, 2009, 7:25pm UTC](https://forum.inductiveautomation.com/t/prepared-statements/1093 "2009-04-17T19:25:02Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![TimE](https://avatars.discourse-cdn.com/v4/letter/t/ee59a6/32.png) [@TimE](https://forum.inductiveautomation.com/u/TimE)\ **Post date:** [April 17, 2009, 7:25pm UTC](https://forum.inductiveautomation.com/t/prepared-statements/1093/1 "2009-04-17T19:25:02Z")
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] ---
**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\ **Post date:** [April 17, 2009, 8:11pm UTC](https://forum.inductiveautomation.com/t/prepared-statements/1093/2 "2009-04-17T20:11:59Z")
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. ---
**Author:** ![TimE](https://avatars.discourse-cdn.com/v4/letter/t/ee59a6/32.png) [@TimE](https://forum.inductiveautomation.com/u/TimE)\ **Post date:** [April 17, 2009, 8:19pm UTC](https://forum.inductiveautomation.com/t/prepared-statements/1093/3 "2009-04-17T20:19:19Z")
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? ---
**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\ **Post date:** [April 17, 2009, 8:37pm UTC](https://forum.inductiveautomation.com/t/prepared-statements/1093/4 "2009-04-17T20:37:51Z")
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. ---
**Author:** ![Jonathan](https://avatars.discourse-cdn.com/v4/letter/j/aeb1de/32.png) [@Jonathan](https://forum.inductiveautomation.com/u/Jonathan)\ **Post date:** [October 23, 2009, 10:44pm UTC](https://forum.inductiveautomation.com/t/prepared-statements/1093/5 "2009-10-23T22:44:03Z")
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] ---
**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\ **Post date:** [October 23, 2009, 11:37pm UTC](https://forum.inductiveautomation.com/t/prepared-statements/1093/6 "2009-10-23T23:37:25Z")
Yeah, prepared selects would be the best way. In the meantime - you need to escape (sanitize) user input. Check out the SQL escape goodie [inductiveautomation.com/prod … dies/?id=7](http://www.inductiveautomation.com/products/factorypmi/goodies/?id=7) ---
**Author:** ![cmaynes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cmaynes/32/6852_2.png) [@cmaynes](https://forum.inductiveautomation.com/u/cmaynes)\ **Post date:** [August 6, 2020, 1:55am UTC](https://forum.inductiveautomation.com/t/prepared-statements/1093/7 "2020-08-06T01:55:42Z")
broken link