August 4, 2007
Using prepared statements in Sleep
Prepared statements can improve performance and secure your applications against SQL injections. With the 1.0 release of Slumber, it is easy to create and execute prepared statements. This article will demonstrate how to use prepared statements.
First of all, import the JDBC extension and connect to the database:
- $connection = dbConnect('com.mysql.jdbc.Driver', 'jdbc:mysql://host/db', 'user', 'pass');
Then create a new prepared statement using the dbPrepare() function:
- $update = "update table set name = ?, address = ? where id = ?";
- $stmt = dbPrepare($connection, $update);
It is important to note the question marks in the statement - these are placeholders for the values we will supply shortly. Note how this entirely eliminates the need to concatenate strings and add custom escaping. Also note that we must supply a connection to associate with the statement.
We supply values for the statement using the function dbSet():
- dbSet($stmt, 1, "John Doe"); # Name
- dbSet($stmt, 2, "Wall street"); # Address
- dbSet($stmt, 3, 4); # ID
Notice how we set a value on a specific statement, and on specific placeholders 1, 2, and 3, corresponding to the order of the question marks in the statement we defined above — exactly like ordinary prepared statements in Java!
Time to execute our prepared statement, which is quite simple:
- $count = dbExecute($stmt);
That’s it! This will execute our update statement, and put the update count in the $count variable. If this prepared statement was a query instead of an update, the dbExecute() function would in fact return a result set, which you could fetch using the ordinary fetch functions.
( sorry this isnt german,dutch,swedish,finnish or what ever language you speak, im saying this because some of the buttons are in another language )
well now i am somewhat new to this and i have used the search engine, but i havent seen a server1 to server2 relay, now maybe someone asked this and you’ve declined or something, and yes i have seen it done in mIRC scripting language but i havent seen one that has a safe usage, they all used something that someone could easily get into and fromat your harddrive or do something a little more evil, and yes i am somewhat new to the sleep language, bu i am doing what i can to learn although i come to a stopping point ive been busy with other matters and havent had the time to study
sorry abut this but i figured out what language this blog is being hosted in
norway, Norwegian ish
oh my, i maye have made a mistake, this isnt the Sleep blog, isnt it?
I’m sorry about the confusing language situation - this is an english blog, however I am a norwegian citizen
And no, this is not the Sleep blog. I merely work on some extensions for Sleep.
Raphael used to have an official “developer blog” for Sleep/jIRCii, but I think maybe it got lost in the recent disk crash on hick.org.
Hi Andreas,
I know what slumber is, and I use Sleep, but what is sleepy? By the way, using slumber and it is working great. Haven’t actually used prepared statements, but that is in the works soon. Thanks fot the great bridge)
marty
Hi Marty! Sleepy was a pet project last year, where me and a friend investigated the possibility of coupling Sleep with Jetty to create an environment for writing web applications with Sleep.
Basically you would write html pages with inline Sleep scripts, kind of like PHP and JSP. These pages were parsed with a parser generated by JFlex, and executed as regular Sleep scripts. We didn’t complete the project, but it was quite interesting and worked (and likely still does) very well. Together with my JDBC bridge and MySQL I wrote a threaded discussion forum as an example of usage.
You can get the latest Sleepy source code here: http://developer.berlios.de/projects/sleepy
And here is the mentioned forum script I wrote: http://printf.no/sleepy/
I want to revive this idea some time, which is why I mentioned it on my blog. Good to hear that Slumber is working as intended
Hi Andreas,
Just curious but have you tried using the alpha version of slumber with sleep 2.1 b20? have been having a few issues with that configuration. Backed off to an older version of sleep and all worked fine. Have a GREAT day!
later,
marty.
Hm, I believe I downloaded it at ran the regression tests without problem at the time, however I shall doublecheck this right away. I’ll get back to you and we’ll figure this out ASAP.
Hi Andreas,
Raph made a change to Slumber for me last night. Slumber would not compile without his mod. He said something about accessing a protected method or something. The method was a clone method if I remember correctly - line 722. With that change made everything seemed to work fine. Have to have my sleep and slumber). Have a GREAT day!
later,
marty.