Skip to content
Browse files

Merge pull request #17 from kimmel/master

Added notes on Perl taint mode. Converted from Textile to Markdown. Added a content license.
  • Loading branch information...
2 parents 119c9fb + fb2ad31 commit f3e3e4067720c38ebdd4219161c93675f07425b2 @petdance committed
Showing with 689 additions and 692 deletions.
  1. +6 −6 Makefile
  2. +7 −3 README.markdown
  3. +9 −21 crank
  4. +14 −0 s/asp.md
  5. +0 −14 s/asp.textile
  6. +33 −0 s/coldfusion.md
  7. +0 −33 s/coldfusion.textile
  8. +22 −0 s/csharp.md
  9. +0 −23 s/csharp.textile
  10. +10 −0 s/delphi.md
  11. +0 −11 s/delphi.textile
  12. +17 −0 s/dotnet.md
  13. +0 −16 s/dotnet.textile
  14. +86 −0 s/index.md
  15. +0 −77 s/index.textile
  16. +50 −0 s/java.md
  17. +0 −48 s/java.textile
  18. +44 −0 s/perl.md
  19. +0 −30 s/perl.textile
  20. +64 −0 s/php.md
  21. +0 −73 s/php.textile
  22. +198 −0 s/plsql.md
  23. +0 −212 s/plsql.textile
  24. +75 −0 s/postgresql.md
  25. +0 −71 s/postgresql.textile
  26. +17 −0 s/python.md
  27. +0 −19 s/python.textile
  28. +18 −0 s/ruby.md
  29. +0 −19 s/ruby.textile
  30. +18 −0 s/scheme.md
  31. +0 −16 s/scheme.textile
  32. +1 −0 tt/footer.tt
View
12 Makefile
@@ -31,14 +31,14 @@ test: crank
messages:
# wrap textile paragraphs into TT loc fragments
- for textilefile in $(SOURCE)/*.textile ; do \
- perl -lne'BEGIN {$$/ = "\n\n";}; print "[% |loc %]$${_}[% END %]\n"' \
- < $$textilefile > $$textilefile.tt2 ; done
+# for textilefile in $(SOURCE)/*.textile ; do \
+# perl -lne'BEGIN {$$/ = "\n\n";}; print "[% |loc %]$${_}[% END %]\n"' \
+# < $$textilefile > $$textilefile.tt2 ; done
# extract string literals to po template
- xgettext.pl -g -u -P tt2 -o $(POTEMPLATE) tt/* $(SOURCE)/*
+# xgettext.pl -g -u -P tt2 -o $(POTEMPLATE) tt/* $(SOURCE)/*
# update po files
- for pofile in $(LOCALE)/*.po ; do \
- msgmerge -U $$pofile $(POTEMPLATE) ; done
+# for pofile in $(LOCALE)/*.po ; do \
+# msgmerge -U $$pofile $(POTEMPLATE) ; done
# This is only useful for Andy
rsync:
View
10 README.markdown
@@ -1,7 +1,11 @@
+This project is the source code for http://bobby-tables.com/, plus the
+Perl code that converts it from Markdown format into HTML and uploads
+it to the server.
+
Repository layout
-----------------
- s page bodies in Textile format
+ s page bodies in Markdown format
tt templates in Template::Toolkit format
static images and styles
share/locale translations in gettext format
@@ -19,7 +23,7 @@ For building:
Locale::Maketext::Lexicon
Locale::Maketext
Template
- Text::Textile
+ Text::Markdown
For testing:
@@ -31,7 +35,7 @@ Contributing page content
1. Modify templates or page bodies. New pages have to be registered in the file `crank`.
2. Run `make` to build the site and inspect the result in the `build` directory.
3. Run `make test` to check for HTML errors.
-4. Commit/publish changes, see `s/index.textile`.
+4. Commit/publish changes, see `s/index.md`.
Contributing translations
-------------------------
View
30 crank
@@ -8,7 +8,7 @@ eval 'use Carp::Always'; # Not everyone has it
use Getopt::Long;
use File::Slurp;
use Encode qw(decode_utf8);
-use Text::Textile ();
+use Text::Markdown ();
use Template ();
use Template::Constants qw( :debug :chomp );
@@ -49,8 +49,7 @@ my $pages = [
MAIN: {
my $handle = Local::L10n->get_handle; # set LANG to localise
- my $textile = Text::Textile::Bobby->new(disable_encode_entities => 1);
- $textile->flavor( 'xhtml1' );
+ my $m = Text::Markdown->new;
my @sidelinks;
@@ -95,12 +94,15 @@ MAIN: {
while ( @pages ) {
my ($section,$desc) = splice( @pages, 0, 2 );
- my $source = read_file( "$sourcepath/$section.textile.tt2" );
+ my $source = read_file( "$sourcepath/$section.md" );
my $first_pass;
$tt_first_pass->process( \$source, undef, \$first_pass )
- || die sprintf("file: %s\nerror: %s\n", "$sourcepath/$section.textile.tt2", $tt->error);
- $vars->{body} = $textile->process( decode_utf8($first_pass) );
- $vars->{body} =~ s{<code>\n}{<code>}smxg;
+ || die sprintf("file: %s\nerror: %s\n", "$sourcepath/$section.md", $tt->error);
+
+ my $html = $m->markdown(decode_utf8($first_pass));
+ $html =~ s{<code>\n}{<code>}smxg;
+ $vars->{body} = $html;
+
$vars->{currlang} = ( $desc eq 'Home' ) ? '' : $desc;
$vars->{rel_static} = ( 'C' eq $ENV{LANG} ) ? q() : q(../); # path prefix to static assets
$vars->{rfc_1766_lang} = ( 'C' eq $ENV{LANG} ) ? 'en' : [map {tr/_/-/;$_} $ENV{LANG}]->[0];
@@ -108,17 +110,3 @@ MAIN: {
|| die sprintf("file: %s\nerror: %s\n", "$section.html", $tt->error);
}
}
-
-package Text::Textile::Bobby;
-
-use base 'Text::Textile';
-
-# This is for inline code formatting
-sub format_code {
- my $self = shift;
-
- my $text = $self->SUPER::format_code( @_ );
- $text =~ s/<code>/<code class="inline">/g;
-
- return $text;
-}
View
14 s/asp.md
@@ -0,0 +1,14 @@
+ASP
+===
+
+ objCmd.CommandType = adCmdText;
+ objCmd.CommandText = "UPDATE members SET photo = @filename WHERE memberID = @memberID";
+ objCmd.Parameters.Append(objCmd.CreateParameter("@memberID", adInteger, adParamInput, 4, memberid ));
+ objCmd.Parameters.Append(objCmd.CreateParameter("@filename", adVarChar, adParamInput, 510, fileName));
+ objCmd.Execute(adExecuteNoRecords);
+ gblDelobjParams(objCmd);
+
+To do
+-----
+
+Add some narrative
View
14 s/asp.textile
@@ -1,14 +0,0 @@
-h1. ASP
-
-<code>
-objCmd.CommandType = adCmdText;
-objCmd.CommandText = "UPDATE members SET photo = @filename WHERE memberID = @memberID";
-objCmd.Parameters.Append(objCmd.CreateParameter("@memberID", adInteger, adParamInput, 4, memberid ));
-objCmd.Parameters.Append(objCmd.CreateParameter("@filename", adVarChar, adParamInput, 510, fileName));
-objCmd.Execute(adExecuteNoRecords);
-gblDelobjParams(objCmd);
-</code>
-
-h2. To do
-
-Add some narrative
View
33 s/coldfusion.md
@@ -0,0 +1,33 @@
+ColdFusion
+==========
+
+In ColdFusion there is a tag called <code class="inline">cfqueryparam</code> that should be used whenever writing inline queries.
+
+ <cfquery name="queryTest">
+ SELECT FirstName, LastName, Phone
+ FROM tblUser
+ WHERE Status =
+ <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.status#">
+ </cfquery>
+
+
+Stored procedures can be invoked with the <code class="inline">cfstoredproc</code> and <code class="inline">cfprocparam</code> tags.
+
+Recent versions of ColdFusion provide a set of functions to run queries that
+have a slightly different syntax, but still provide parameterized queries.
+
+
+ <cfscript>
+ var myQuery = new Query(sql="
+ SELECT FirstName, LastName, Phone
+ FROM tblUser
+ WHERE Status = :status
+ ");
+ myQuery.addParam(
+ name = "status",
+ value = form.status,
+ cfsqltype = "cf_sql_varchar"
+ );
+ var rawQuery = myQuery.execute().getResult();
+ </cfscript>
+
View
33 s/coldfusion.textile
@@ -1,33 +0,0 @@
-h1. ColdFusion
-
-In ColdFusion there is a tag called @cfqueryparam@ that should be used whenever writing inline queries.
-
-<code>
-&lt;cfquery name="queryTest">
-SELECT FirstName, LastName, Phone
-FROM tblUser
-WHERE Status =
- &lt;cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.status#">
-&lt;/cfquery>
-</code>
-
-Stored procedures can be invoked with the @cfstoredproc@ and @cfprocparam@ tags.
-
-Recent versions of ColdFusion provide a set of functions to run queries that
-have a slightly different syntax, but still provide parameterized queries.
-
-<code>
- &lt;cfscript>
- var myQuery = new Query(sql="
- SELECT FirstName, LastName, Phone
- FROM tblUser
- WHERE Status = :status
- ");
- myQuery.addParam(
- name = "status",
- value = form.status,
- cfsqltype = "cf_sql_varchar"
- );
- var rawQuery = myQuery.execute().getResult();
- &lt;/cfscript>
-</code>
View
22 s/csharp.md
@@ -0,0 +1,22 @@
+C\#
+===
+
+From the [C# Online](http://en.csharp-online.net/) wiki page [ASP.NET Security Hacks--Avoiding SQL Injection](http://en.csharp-online.net/ASP.NET_Security_Hacks%E2%80%94Avoiding_SQL_Injection)
+
+
+ SqlCommand userInfoQuery = new SqlCommand(
+ "SELECT id, name, email FROM users WHERE id = @UserName",
+ someSqlConnection);
+
+ SqlParameter userNameParam = userInfoQuery.Parameters.Add("@UserName",
+ SqlDbType.VarChar, 25 /* max length of field */ );
+
+ // userName is some string valued user input variable
+ userNameParam.Value = userName;
+
+Or simpler:
+
+
+ String username = "joe.bloggs";
+ SqlCommand sqlQuery = new SqlCommand("SELECT user_id, first_name,last_name FROM users WHERE username = ?username", sqlConnection);
+ sqlQuery.Parameters.AddWithValue("?username", username);
View
23 s/csharp.textile
@@ -1,23 +0,0 @@
-h1. C#
-
-From the "C# Online":http://en.csharp-online.net/ wiki page "ASP.NET Security Hacks--Avoiding SQL Injection":http://en.csharp-online.net/ASP.NET_Security_Hacks%E2%80%94Avoiding_SQL_Injection
-
-<code>
-SqlCommand userInfoQuery = new SqlCommand(
- "SELECT id, name, email FROM users WHERE id = @UserName",
- someSqlConnection);
-
-SqlParameter userNameParam = userInfoQuery.Parameters.Add("@UserName",
- SqlDbType.VarChar, 25 /* max length of field */ );
-
-// userName is some string valued user input variable
-userNameParam.Value = userName;
-</code>
-
-Or simpler:
-
-<code>
-String username = "joe.bloggs";
-SqlCommand sqlQuery = new SqlCommand("SELECT user_id, first_name,last_name FROM users WHERE username = ?username", sqlConnection);
-sqlQuery.Parameters.AddWithValue("?username", username);
-</code>
View
10 s/delphi.md
@@ -0,0 +1,10 @@
+Delphi
+======
+
+To use a prepared statement, do something like this:
+
+ query.SQL.Text := 'update people set name=:Name where id=:ID';
+ query.Prepare;
+ query.ParamByName( 'Name' ).AsString := name;
+ query.ParamByName( 'ID' ).AsInteger := id;
+ query.ExecSQL;
View
11 s/delphi.textile
@@ -1,11 +0,0 @@
-h1. Delphi
-
-To use a prepared statement, do something like this:
-
-<code>
-query.SQL.Text := 'update people set name=:Name where id=:ID';
-query.Prepare;
-query.ParamByName( 'Name' ).AsString := name;
-query.ParamByName( 'ID' ).AsInteger := id;
-query.ExecSQL;
-</code>
View
17 s/dotnet.md
@@ -0,0 +1,17 @@
+.NET
+====
+
+Reference:
+
+- [SqlCommand.Prepare](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.prepare.aspx) in the .NET Framework Class Library
+
+Articles:
+
+- [SQL injection](http://msdn.microsoft.com/en-us/library/ms161953.aspx) on MSDN
+- [SQL Injection and how to avoid it](http://blogs.msdn.com/tom/archive/2008/05/29/sql-injection-and-how-to-avoid-it.aspx) on the ASP.NET Debugging blog
+
+To do
+-----
+
+- Add some narrative
+- Show code examples
View
16 s/dotnet.textile
@@ -1,16 +0,0 @@
-h1. .NET
-
-Reference:
-
-* "SqlCommand.Prepare":http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.prepare.aspx in the .NET Framework Class Library
-
-Articles:
-
-* "SQL injection":http://msdn.microsoft.com/en-us/library/ms161953.aspx on MSDN
-* "SQL Injection and how to avoid it":http://blogs.msdn.com/tom/archive/2008/05/29/sql-injection-and-how-to-avoid-it.aspx on the ASP.NET Debugging blog
-
-h2. To do
-
-* Add some narrative
-* Show code examples
-
View
86 s/index.md
@@ -0,0 +1,86 @@
+Who is Bobby Tables?
+====================
+
+<p>
+<a href="http://xkcd.com/327/"><img src="img/xkcd.png" alt="xkcd Bobby Tables Cartoon" /></a>
+<a href="http://xkcd.com/327/">From the comic strip xkcd</a><br />
+<b>School</b>: "Hi, this is your son's school. We're having some computer trouble."<br />
+<b>Mom</b>: "Oh, dear -- Did he break something?"<br />
+<b>School</b>: "In a way. Did you really name your son Robert'); DROP TABLE Students;-- ?"<br />
+<b>Mom</b>: "Oh. Yes. Little Bobby Tables we call him."<br />
+<b>School</b>: "Well, we've lost this year's student records. I hope you're happy."<br />
+<b>Mom</b>: "And I hope you've learned to sanitize your database inputs."<br />
+(title text: "Her daughter is named Help I'm trapped in a driver's license factory.")
+</p>
+
+How to avoid Bobby Tables
+=========================
+
+There is only one way to avoid Bobby Tables attacks
+
+- Do not create SQL statements that include outside data.
+- Use parameterized SQL calls.
+
+That's it. Don't try to escape invalid characters. Don't try to do it yourself. Learn how to use parameterized statements. Always, every single time.
+
+The strip gets one thing crucially wrong. The answer is not to "sanitize your database inputs" yourself. It is prone to error.
+
+Examples
+========
+
+See the sidebar to the left for your specific language.
+
+Other random resources
+======================
+
+- [SQL Injection Myths and Fallacies](http://www.slideshare.net/billkarwin/sql-injection-myths-and-fallacies)
+- [http://www.schneier.com/blog/archives/2008/10/how_to_write_in.html](http://www.schneier.com/blog/archives/2008/10/how_to_write_in.html)
+- [http://st-curriculum.oracle.com/tutorial/SQLInjection/](http://st-curriculum.oracle.com/tutorial/SQLInjection/)
+
+Patches welcome
+===============
+
+Don't see a language that you'd like to see represented? Please let me know if you have updates or additions through one of these methods, in decreasing order of preference.
+
+- Fork the [bobby-tables repository at github](http://github.com/petdance/bobby-tables), make your changes, and send me a pull request.
+- Add an issue in the [issue tracker](http://github.com/petdance/bobby-tables/issues).
+- Email me, Andy Lester, at andy at petdance.com.
+
+Translations also welcome
+=========================
+
+Help translate this site! There are only 100 phrases. No programming necessary.
+
+See the instructions at the [bobby-tables repository at github](http://github.com/petdance/bobby-tables#readme).
+
+To do
+=====
+
+- Explain why creating code from outside data is bad.
+- Potential speed win when reusing prepared statements.
+
+Thanks
+======
+
+Thanks to the following folks for their contributions:
+
+- Kirk Kimmel
+- Nathan Mahdavi
+- [Hannes Hofmann](http://www5.informatik.uni-erlangen.de/en/our-team/hofmann-hannes)
+- [Mike Angstadt](http://www.mangst.com)
+- [Peter Ward](http://identi.ca/flowblok/)
+- [David Wheeler](http://justatheory.com)
+- Scott Rose
+- Erik Osheim
+- Russ Sivak
+- [Iain Collins](http://iaincollins.com)
+- Kristoffer Sall Hansen
+- Jeff Emminger
+- [Travis Swicegood](http://www.travisswicegood.com/)
+- [Will Coleda](http://www.coleda.com/users/coke/)
+- Kai Baesler
+- Mike Markley
+- [Michael Schwern](http://schwern.dreamhosters.com/)
+- [Jeana Clark](http://jeanaclark.org/)
+- [Lars Dɪᴇᴄᴋᴏᴡ](http://search.cpan.org/~daxim/)
+- [Jani Hur](http://www.jani-hur.net)
View
77 s/index.textile
@@ -1,77 +0,0 @@
-h1. Who is Bobby Tables?
-
-<p>
-<a href="http://xkcd.com/327/"><img src="img/xkcd.png" alt="xkcd Bobby Tables Cartoon" /></a>
-<a href="http://xkcd.com/327/">From the comic strip xkcd</a><br />
-<b>School</b>: "Hi, this is your son's school. We're having some computer trouble."<br />
-<b>Mom</b>: "Oh, dear -- Did he break something?"<br />
-<b>School</b>: "In a way. Did you really name your son Robert'); DROP TABLE Students;-- ?"<br />
-<b>Mom</b>: "Oh. Yes. Little Bobby Tables we call him."<br />
-<b>School</b>: "Well, we've lost this year's student records. I hope you're happy."<br />
-<b>Mom</b>: "And I hope you've learned to sanitize your database inputs."<br />
-(title text: "Her daughter is named Help I'm trapped in a driver's license factory.")
-</p>
-
-h1. How to avoid Bobby Tables
-
-There is only one way to avoid Bobby Tables attacks
-
-* Do not create SQL statements that include outside data.
-* Use parameterized SQL calls.
-
-That's it. Don't try to escape invalid characters. Don't try to do it yourself. Learn how to use parameterized statements. Always, every single time.
-
-The strip gets one thing crucially wrong. The answer is not to "sanitize your database inputs" yourself. It is prone to error.
-
-h1. Examples
-
-See the sidebar to the left for your specific language.
-
-h1. Other random resources
-
-* "SQL Injection Myths and Fallacies":http://www.slideshare.net/billkarwin/sql-injection-myths-and-fallacies
-* "http://www.schneier.com/blog/archives/2008/10/how_to_write_in.html":http://www.schneier.com/blog/archives/2008/10/how_to_write_in.html
-* "http://st-curriculum.oracle.com/tutorial/SQLInjection/":http://st-curriculum.oracle.com/tutorial/SQLInjection/
-
-h1. Patches welcome
-
-Don't see a language that you'd like to see represented? Please let me know if you have updates or additions through one of these methods, in decreasing order of preference.
-
-* Fork the "bobby-tables repository at github":http://github.com/petdance/bobby-tables, make your changes, and send me a pull request.
-* Add an issue in the "issue tracker":http://github.com/petdance/bobby-tables/issues.
-* Email me, Andy Lester, at andy at petdance.com.
-
-h1. Translations also welcome
-
-Help translate this site! There are only 100 phrases. No programming necessary.
-
-See the instructions at the "bobby-tables repository at github":http://github.com/petdance/bobby-tables#readme.
-
-h1. To do
-
-* Explain why creating code from outside data is bad.
-* Potential speed win when reusing prepared statements.
-
-h1. Thanks
-
-Thanks to the following folks for their contributions:
-
-* Nathan Mahdavi
-* "Hannes Hofmann":http://www5.informatik.uni-erlangen.de/en/our-team/hofmann-hannes
-* "Mike Angstadt":http://www.mangst.com
-* "Peter Ward":http://identi.ca/flowblok/
-* "David Wheeler":http://justatheory.com
-* Scott Rose
-* Erik Osheim
-* Russ Sivak
-* "Iain Collins":http://iaincollins.com
-* Kristoffer Sall Hansen
-* Jeff Emminger
-* "Travis Swicegood":http://www.travisswicegood.com/
-* "Will Coleda":http://www.coleda.com/users/coke/
-* Kai Baesler
-* Mike Markley
-* "Michael Schwern":http://schwern.dreamhosters.com/
-* "Jeana Clark":http://jeanaclark.org/
-* "Lars Dɪᴇᴄᴋᴏᴡ":http://search.cpan.org/~daxim/
-* "Jani Hur":http://www.jani-hur.net
View
50 s/java.md
@@ -0,0 +1,50 @@
+Java
+====
+
+JDBC
+----
+
+The [JDBC API](http://download.oracle.com/javase/tutorial/jdbc/index.html) has a class called <code class="inline">[PreparedStatement](http://download.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html)</code>, which allows the programmer to safely insert user-supplied data into a SQL query. The location of each input value in the query string is marked with a question mark. The various <code class="inline">set*()</code> methods are then used to safely perform the insertion.
+
+ String name = //user input
+ int age = //user input
+ Connection connection = DriverManager.getConnection(...);
+ PreparedStatement statement = connection.prepareStatement("SELECT * FROM people WHERE lastName = ? AND age > ?");
+ statement.setString(1, name); //lastName is a VARCHAR
+ statement.setInt(2, age); //age is an INT
+ ResultSet rs = statement.executeQuery();
+ while (rs.next()){
+ //...
+ }
+
+
+Once a <code class="inline">PreparedStatement</code> object has been created, it can be reused multiple times for multiple queries (for example, when using the same query to update multiple rows in a table). However, they are **not thread-safe** because of the many method calls involved in setting the parameters and executing the query. Therefore, you should only define <code class="inline">PreparedStatement</code> objects as method-level variables (as opposed to class-level variables) to avoid concurrency issues.
+
+
+ List<Person>; people = //user input
+ Connection connection = DriverManager.getConnection(...);
+ connection.setAutoCommit(false);
+ try {
+ PreparedStatement statement = connection.prepareStatement("UPDATE people SET lastName = ?, age = ? WHERE id = ?");
+ for (Person person : people){
+ statement.setString(1, person.getLastName());
+ statement.setInt(2, person.getAge());
+ statement.setInt(3, person.getId());
+ statement.execute();
+ }
+ connection.commit();
+ } catch (SQLException e) {
+ connection.rollback();
+ }
+
+More information on <code class="inline">PreparedStatement</code> can be found in the [Oracle JDBC tutorial](http://download.oracle.com/javase/tutorial/jdbc/basics/prepared.html).
+
+Hibernate
+---------
+
+[Hibernate](http://www.hibernate.org/) also supports prepared statements. See the [Hibernate Manual](http://docs.jboss.org/hibernate/stable/core/reference/en/html/objectstate.html#objectstate-querying-executing-parameters) for more information.
+
+MyBatis
+-------
+
+TODO
View
48 s/java.textile
@@ -1,48 +0,0 @@
-h1. Java
-
-h2. JDBC
-
-The "JDBC API":http://download.oracle.com/javase/tutorial/jdbc/index.html has a class called "@PreparedStatement@":http://download.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html, which allows the programmer to safely insert user-supplied data into a SQL query. The location of each input value in the query string is marked with a question mark. The various @set*()@ methods are then used to safely perform the insertion.
-
-<pre>
-String name = //user input
-int age = //user input
-Connection connection = DriverManager.getConnection(...);
-PreparedStatement statement = connection.prepareStatement("SELECT * FROM people WHERE lastName = ? AND age > ?");
-statement.setString(1, name); //lastName is a VARCHAR
-statement.setInt(2, age); //age is an INT
-ResultSet rs = statement.executeQuery();
-while (rs.next()){
- //...
-}
-</pre>
-
-Once a @PreparedStatement@ object has been created, it can be reused multiple times for multiple queries (for example, when using the same query to update multiple rows in a table). However, they are *not thread-safe* because of the many method calls involved in setting the parameters and executing the query. Therefore, you should only define @PreparedStatement@ objects as method-level variables (as opposed to class-level variables) to avoid concurrency issues.
-
-<pre>
-List&lt;Person&gt; people = //user input
-Connection connection = DriverManager.getConnection(...);
-connection.setAutoCommit(false);
-try {
- PreparedStatement statement = connection.prepareStatement("UPDATE people SET lastName = ?, age = ? WHERE id = ?");
- for (Person person : people){
- statement.setString(1, person.getLastName());
- statement.setInt(2, person.getAge());
- statement.setInt(3, person.getId());
- statement.execute();
- }
- connection.commit();
-} catch (SQLException e) {
- connection.rollback();
-}
-</pre>
-
-More information on @PreparedStatement@ can be found in the "Oracle JDBC tutorial":http://download.oracle.com/javase/tutorial/jdbc/basics/prepared.html.
-
-h2. Hibernate
-
-"Hibernate":http://www.hibernate.org/ also supports prepared statements. See the "Hibernate Manual":http://docs.jboss.org/hibernate/stable/core/reference/en/html/objectstate.html#objectstate-querying-executing-parameters for more information.
-
-h2. MyBatis
-
-TODO
View
44 s/perl.md
@@ -0,0 +1,44 @@
+Perl
+====
+
+Perl's [DBI](http://search.cpan.org/dist/DBI), available on the [CPAN](http://search.cpan.org), supports parameterized SQL calls. Both the <code class="inline">do</code> method and <code class="inline">prepare</code> method support parameters ("placeholders", as they call them) for most database drivers. For example:
+
+
+ $sth = $dbh->prepare("SELECT * FROM users WHERE email = ?");
+ foreach my $email (@emails) {
+ $sth->execute($email);
+ $row = $sth->fetchrow_hashref;
+ [...]
+ }
+
+However, you can't use parameterization for identifiers (table names, column names) so you need to use DBI's <tt>quote_identifier()</tt> method for that:
+
+ # Make sure a table name we want to use is safe:
+ my $quoted_table_name = $dbh->quote_identifier($table_name);
+
+ # Assume @cols contains a list of column names you need to fetch:
+ my $cols = join ',', map { $dbh->quote_identifier($_) } @cols;
+
+ my $sth = $dbh->prepare("SELECT $cols FROM $quoted_table_name ...");
+
+You could also avoid writing SQL by hand by using [DBIx::Class](http://p3rl.org/DBIx::Class), [SQL::Abstract](http://p3rl.org/SQL::Abstract) etc to generate your SQL for you programmatically.
+
+What is Taint mode?
+-------------------
+
+Taint mode is a special set of security checks that Perl performs on data input into your program from external sources. The input data is marked as tainted (untrusted) and may not be used in commands that would allow you to shoot yourself in the foot. See perldoc perlsec for a detailed breakdown of what taint mode tracks.
+
+To invoke taint mode:
+
+ # From the command line
+ perl -T program.pl
+
+ # At the top of your script
+ #!/usr/bin/perl -T
+
+When your script trips one of the taint checks your application will issue a fatal error message. For testing purposes '-t' will issue warnings instead of fatal errors. '-t' is not a substitute for '-T'.
+
+To do
+-----
+
+Explain how DBI supports taint mode, both inbound and outbound.
View
30 s/perl.textile
@@ -1,30 +0,0 @@
-h1. Perl
-
-Perl's "DBI":http://search.cpan.org/dist/DBI, available on the "CPAN":http://search.cpan.org, supports parameterized SQL calls. Both the @do@ method and @prepare@ method support parameters ("placeholders", as they call them) for most database drivers. For example:
-
-<code>
-$sth = $dbh->prepare("SELECT * FROM users WHERE email = ?");
-foreach my $email (@emails) {
- $sth->execute($email);
- $row = $sth->fetchrow_hashref;
- [...]
-}
-</code>
-
-However, you can't use parameterization for identifiers (table names, column names) so you need to use DBI's <tt>quote_identifier()</tt> method for that:
-
-<code>
-# Make sure a table name we want to use is safe:
-my $quoted_table_name = $dbh->quote_identifier($table_name);
-
-# Assume @cols contains a list of column names you need to fetch:
-my $cols = join ',', map { $dbh->quote_identifier($_) } @cols;
-
-my $sth = $dbh->prepare("SELECT $cols FROM $quoted_table_name ...");
-</code>
-
-You could also avoid writing SQL by hand by using "DBIx::Class":http://p3rl.org/DBIx::Class, "SQL::Abstract":http://p3rl.org/SQL::Abstract etc to generate your SQL for you programmatically.
-
-h2. To do
-
-Explain Perl's taint mode and how DBI supports taint mode, both inbound and outbound.
View
64 s/php.md
@@ -0,0 +1,64 @@
+PHP
+===
+
+PHP is a little more disorganized than how [Perl handles parameters](./perl.html). The standard [MySQL extension](http://php.net/manual/en/book.mysql.php) doesn't support parameterization, although the [PostgreSQL extension](http://www.php.net/manual/en/book.pgsql.php) does:
+
+ $result = pg_query_params( $dbh, 'SELECT * FROM users WHERE email = $1', array($email) );
+
+Note that the query must be in single-quotes or have the <code class="inline">$</code> escaped to avoid PHP trying to parse it as a variable.
+
+**However**, you should probably be using an abstraction layer. The [ODBC](http://php.net/manual/en/book.uodbc.php) and [PDO](http://www.php.net/manual/en/book.pdo.php) extensions both support parameterization and multiple databases:
+
+Using mysqli
+------------
+
+The MySQL Improved extension handles bound parameters.
+
+ $stmt = $db->prepare('update people set name = ? where id = ?');
+ $stmt->bind_param('si',$name,$id);
+ $stmt->execute();
+
+Using ADODB
+-----------
+
+ADODB provides a way to prepare, bind and execute all in the same method call.
+
+ $dbConnection = NewADOConnection($connectionString);
+ $sqlResult = $dbConnection->Execute(
+ 'SELECT user_id,first_name,last_name FROM users WHERE username=? AND password=?',
+ array($_REQUEST['username'], sha1($_REQUEST['password'])
+ );
+
+Using the ODBC layer
+--------------------
+
+ $stmt = odbc_prepare( $conn, 'SELECT * FROM users WHERE email = ?' );
+ $success = odbc_execute( $stmt, array($email) );
+
+Or:
+
+ $res = odbc_exec($conn, 'SELECT * FROM users WHERE email = ?', array($email));
+
+ $sth = $dbh->prepare('SELECT * FROM users WHERE email = :email');
+ $sth->execute(array(':email' => $email));
+
+Using the PDO layer
+-------------------
+
+Here's the long way to do bind parameters.
+
+ $dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', $user, $password);
+ $stmt = $dbh->prepare('INSERT INTO REGISTRY (name, value) VALUES (:name, :value)');
+ $stmt->bindParam(':name', $name);
+ $stmt->bindParam(':value', $value);
+
+ // insert one row
+ $name = 'one';
+ $value = 1;
+ $stmt->execute();
+
+And a shorter way to pass things in.
+
+ $dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', $user, $password);
+ $stmt = $dbh->prepare('UPDATE people SET name = :new_name WHERE id = :id');
+ $stmt->execute( array('new_name' => $name, 'id' => $id) );
View
73 s/php.textile
@@ -1,73 +0,0 @@
-h1. PHP
-
-PHP is a little more disorganized than how "Perl handles parameters":./perl.html. The standard "MySQL extension":http://php.net/manual/en/book.mysql.php doesn't support parameterization, although the "PostgreSQL extension":http://www.php.net/manual/en/book.pgsql.php does:
-
-<code>
-$result = pg_query_params( $dbh, 'SELECT * FROM users WHERE email = $1', array($email) );
-</code>
-
-Note that the query must be in single-quotes or have the @$@ escaped to avoid PHP trying to parse it as a variable.
-
-*However*, you should probably be using an abstraction layer. The "ODBC":http://php.net/manual/en/book.uodbc.php and "PDO":http://www.php.net/manual/en/book.pdo.php extensions both support parameterization and multiple databases:
-
-h2. Using mysqli
-
-The MySQL Improved extension handles bound parameters.
-
-<code>
-$stmt = $db->prepare('update people set name = ? where id = ?');
-$stmt->bind_param('si',$name,$id);
-$stmt->execute();
-</code>
-
-h2. Using ADODB
-
-ADODB provides a way to prepare, bind and execute all in the same method call.
-
-<code>
-$dbConnection = NewADOConnection($connectionString);
-$sqlResult = $dbConnection->Execute(
- 'SELECT user_id,first_name,last_name FROM users WHERE username=? AND password=?',
- array($_REQUEST['username'], sha1($_REQUEST['password'])
-);
-</code>
-
-h2. Using the ODBC layer
-
-<code>
-$stmt = odbc_prepare( $conn, 'SELECT * FROM users WHERE email = ?' );
-$success = odbc_execute( $stmt, array($email) );
-</code>
-
-Or:
-
-<code>
-$res = odbc_exec($conn, 'SELECT * FROM users WHERE email = ?', array($email));
-
-$sth = $dbh->prepare('SELECT * FROM users WHERE email = :email');
-$sth->execute(array(':email' => $email));
-</code>
-
-h2. Using the PDO layer
-
-Here's the long way to do bind parameters.
-
-<code>
-$dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', $user, $password);
-$stmt = $dbh->prepare('INSERT INTO REGISTRY (name, value) VALUES (:name, :value)');
-$stmt->bindParam(':name', $name);
-$stmt->bindParam(':value', $value);
-
-// insert one row
-$name = 'one';
-$value = 1;
-$stmt->execute();
-</code>
-
-And a shorter way to pass things in.
-
-<code>
-$dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', $user, $password);
-$stmt = $dbh->prepare('UPDATE people SET name = :new_name WHERE id = :id');
-$stmt->execute( array('new_name' => $name, 'id' => $id) );
-</code>
View
198 s/plsql.md
@@ -0,0 +1,198 @@
+PL/SQL
+======
+
+Examples assume the following table structure:
+
+ CREATE TABLE users (
+ username VARCHAR2(8) UNIQUE,
+ accessed_at DATE,
+ superuser NUMBER(1,0)
+ );
+
+ INSERT INTO users VALUES ('janihur', sysdate, 0);
+ INSERT INTO users VALUES ('petdance', sysdate - 12, 1);
+ INSERT INTO users VALUES ('albundy', sysdate - 3, 0);
+ INSERT INTO users VALUES ('donduck', sysdate - 18, 0);
+
+Always prefer static SQL when possible
+--------------------------------------
+
+Static SQL leaves no room for SQL injection.
+
+ CREATE OR REPLACE FUNCTION user_access (
+ p_uname IN VARCHAR2
+ ) RETURN date AS
+ v_accessed_at date;
+ BEGIN
+ SELECT accessed_at INTO v_accessed_at FROM users WHERE username = p_uname;
+ RETURN v_accessed_at;
+ END;
+ /
+
+
+ SELECT user_access('janihur')
+ AS "JANIHUR LAST SEEN" FROM DUAL;
+
+ JANIHUR LAST SEEN
+ -------------------
+ 2011-08-03 17:11:24
+
+ SELECT user_access('whocares'' or superuser = 1 or username = ''whocares')
+ AS "SUPERUSER LAST SEEN" FROM DUAL;
+
+ SUPERUSER LAST SEEN
+ -------------------
+
+
+If you need dynamic SQL avoid string concatenation when possible
+----------------------------------------------------------------
+
+String concatenation opens doors to possible SQL injection exploits:
+
+ CREATE OR REPLACE FUNCTION user_access (
+ p_uname IN VARCHAR2
+ ) RETURN date AS
+ v_accessed_at date;
+ v_query constant varchar2(32767) :=
+ 'SELECT accessed_at FROM users WHERE username = ''' || p_uname || '''';
+ BEGIN
+ EXECUTE IMMEDIATE v_query INTO v_accessed_at;
+ RETURN v_accessed_at;
+ END;
+ /
+
+
+ SELECT user_access('janihur')
+ AS "JANIHUR LAST SEEN" FROM DUAL;
+
+ JANIHUR LAST SEEN
+ -------------------
+ 2011-08-03 17:11:24
+
+ SELECT user_access('whocares'' or superuser = 1 or username = ''whocares')
+ AS "SUPERUSER LAST SEEN" FROM DUAL;
+
+ SUPERUSER LAST SEEN
+ -------------------
+ 2011-07-22 17:11:24
+
+Instead use bind variables:
+
+
+ CREATE OR REPLACE FUNCTION user_access (
+ p_uname IN VARCHAR2
+ ) RETURN date AS
+ v_accessed_at date;
+ v_query constant varchar2(32767) :=
+ 'SELECT accessed_at FROM users WHERE username = :a';
+ BEGIN
+ EXECUTE IMMEDIATE v_query INTO v_accessed_at USING p_uname;
+ RETURN v_accessed_at;
+ END;
+ /
+
+
+ SELECT user_access('janihur')
+ AS "JANIHUR LAST SEEN" FROM DUAL;
+
+ JANIHUR LAST SEEN
+ -------------------
+ 2011-08-03 17:11:24
+
+ SELECT user_access('whocares'' or superuser = 1 or username = ''whocares')
+ AS "SUPERUSER LAST SEEN" FROM DUAL;
+
+ SUPERUSER LAST SEEN
+ -------------------
+
+Implicit Data Type Conversion Injection
+---------------------------------------
+
+Also NLS session parameters (NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, NLS_NUMERIC_CHARACTER) can be used to modify or inject SQL statements.
+
+In next example data type conversion takes place when @p_since@ is implicitly converted to a string for concatenation. Note how the value of NLS_DATE_FORMAT affects to the query string in users_since()-function !
+
+ ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
+
+ CREATE OR REPLACE TYPE userlist_t AS TABLE OF VARCHAR2(8);
+ /
+
+ CREATE OR REPLACE FUNCTION users_since(
+ p_since IN DATE
+ ) RETURN userlist_t PIPELINED AS
+ v_users userlist_t;
+ v_query constant varchar2(32767) :=
+ 'SELECT username FROM users WHERE superuser = 0 and accessed_at > ''' || p_since || ''' order by accessed_at desc';
+ BEGIN
+ DBMS_OUTPUT.PUT_LINE('v_query = ' || v_query);
+ EXECUTE IMMEDIATE v_query BULK COLLECT INTO v_users;
+
+ FOR i IN v_users.FIRST .. v_users.LAST LOOP
+ PIPE ROW(v_users(i));
+ END LOOP;
+
+ RETURN;
+ END;
+ /
+
+
+ ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD"SUPRISE!"';
+ SELECT COLUMN_VALUE AS "REGULARS" FROM TABLE(users_since(sysdate - 30));
+
+ v_query = SELECT username FROM users WHERE superuser = 0 and accessed_at >
+ '2011-07-04SUPRISE!' order by accessed_at desc
+
+ REGULARS
+ --------
+ janihur
+ albundy
+ donduck
+
+ ALTER SESSION SET NLS_DATE_FORMAT = '"'' or superuser = 1 or username = ''whocares"';
+ SELECT COLUMN_VALUE AS "SUPERUSER IS" FROM TABLE(users_since(sysdate - 30));
+
+ v_query = SELECT username FROM users WHERE superuser = 0 and accessed_at > ''
+ or superuser = 1 or username = 'whocares' order by accessed_at desc
+
+ SUPERUSE
+ --------
+ petdance
+
+The remedy is to set the format modifier explicitly: @to_char(p_since, 'YYYY-MM-DD')@.
+
+ ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
+
+ CREATE OR REPLACE TYPE userlist_t AS TABLE OF VARCHAR2(8);
+ /
+
+ CREATE OR REPLACE FUNCTION users_since(
+ p_since IN DATE
+ ) RETURN userlist_t PIPELINED AS
+ v_users userlist_t;
+ v_query constant varchar2(32767) :=
+ 'SELECT username FROM users WHERE superuser = 0 and accessed_at > ''' || to_char(p_since, 'YYYY-MM-DD') || ''' order by accessed_at desc';
+ BEGIN
+ DBMS_OUTPUT.PUT_LINE('v_query = ' || v_query);
+ EXECUTE IMMEDIATE v_query BULK COLLECT INTO v_users;
+
+ FOR i IN v_users.FIRST .. v_users.LAST LOOP
+ PIPE ROW(v_users(i));
+ END LOOP;
+
+ RETURN;
+ END;
+ /
+
+Now the value of NLS parameter @NLS_DATE_FORMAT@ is ignored during the query.
+
+ ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD"SUPRISE!"';
+ SELECT COLUMN_VALUE AS "REGULARS" FROM TABLE(users_since(sysdate - 30));
+
+ v_query = SELECT username FROM users WHERE superuser = 0 and accessed_at >
+ '2011-07-04' order by accessed_at desc
+
+ REGULARS
+ --------
+ janihur
+ albundy
+ donduck
View
212 s/plsql.textile
@@ -1,212 +0,0 @@
-h1. PL/SQL
-
-Examples assume the following table structure:
-
-<code>
-CREATE TABLE users (
- username VARCHAR2(8) UNIQUE,
- accessed_at DATE,
- superuser NUMBER(1,0)
-);
-
-INSERT INTO users VALUES ('janihur', sysdate, 0);
-INSERT INTO users VALUES ('petdance', sysdate - 12, 1);
-INSERT INTO users VALUES ('albundy', sysdate - 3, 0);
-INSERT INTO users VALUES ('donduck', sysdate - 18, 0);
-</code>
-
-h2. Always prefer static SQL when possible
-
-Static SQL leaves no room for SQL injection.
-
-<code>
-CREATE OR REPLACE FUNCTION user_access (
- p_uname IN VARCHAR2
-) RETURN date AS
- v_accessed_at date;
-BEGIN
- SELECT accessed_at INTO v_accessed_at FROM users WHERE username = p_uname;
- RETURN v_accessed_at;
-END;
-/
-</code>
-
-<code>
-SELECT user_access('janihur')
- AS "JANIHUR LAST SEEN" FROM DUAL;
-
-JANIHUR LAST SEEN
--------------------
-2011-08-03 17:11:24
-
-SELECT user_access('whocares'' or superuser = 1 or username = ''whocares')
- AS "SUPERUSER LAST SEEN" FROM DUAL;
-
-SUPERUSER LAST SEEN
--------------------
-
-</code>
-
-h2. If you need dynamic SQL avoid string concatenation when possible
-
-String concatenation opens doors to possible SQL injection exploits:
-
-<code>
-CREATE OR REPLACE FUNCTION user_access (
- p_uname IN VARCHAR2
-) RETURN date AS
- v_accessed_at date;
- v_query constant varchar2(32767) :=
- 'SELECT accessed_at FROM users WHERE username = ''' || p_uname || '''';
-BEGIN
- EXECUTE IMMEDIATE v_query INTO v_accessed_at;
- RETURN v_accessed_at;
-END;
-/
-</code>
-
-<code>
-SELECT user_access('janihur')
- AS "JANIHUR LAST SEEN" FROM DUAL;
-
-JANIHUR LAST SEEN
--------------------
-2011-08-03 17:11:24
-
-SELECT user_access('whocares'' or superuser = 1 or username = ''whocares')
- AS "SUPERUSER LAST SEEN" FROM DUAL;
-
-SUPERUSER LAST SEEN
--------------------
-2011-07-22 17:11:24
-</code>
-
-Instead use bind variables:
-
-<code>
-CREATE OR REPLACE FUNCTION user_access (
- p_uname IN VARCHAR2
-) RETURN date AS
- v_accessed_at date;
- v_query constant varchar2(32767) :=
- 'SELECT accessed_at FROM users WHERE username = :a';
-BEGIN
- EXECUTE IMMEDIATE v_query INTO v_accessed_at USING p_uname;
- RETURN v_accessed_at;
-END;
-/
-</code>
-
-<code>
-SELECT user_access('janihur')
- AS "JANIHUR LAST SEEN" FROM DUAL;
-
-JANIHUR LAST SEEN
--------------------
-2011-08-03 17:11:24
-
-SELECT user_access('whocares'' or superuser = 1 or username = ''whocares')
- AS "SUPERUSER LAST SEEN" FROM DUAL;
-
-SUPERUSER LAST SEEN
--------------------
-
-</code>
-
-h2. Implicit Data Type Conversion Injection
-
-Also NLS session parameters (NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, NLS_NUMERIC_CHARACTER) can be used to modify or inject SQL statements.
-
-In next example data type conversion takes place when @p_since@ is implicitly converted to a string for concatenation. Note how the value of NLS_DATE_FORMAT affects to the query string in users_since()-function !
-
-<code>
-ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
-
-CREATE OR REPLACE TYPE userlist_t AS TABLE OF VARCHAR2(8);
-/
-
-CREATE OR REPLACE FUNCTION users_since(
- p_since IN DATE
-) RETURN userlist_t PIPELINED AS
- v_users userlist_t;
- v_query constant varchar2(32767) :=
- 'SELECT username FROM users WHERE superuser = 0 and accessed_at > ''' || p_since || ''' order by accessed_at desc';
-BEGIN
- DBMS_OUTPUT.PUT_LINE('v_query = ' || v_query);
- EXECUTE IMMEDIATE v_query BULK COLLECT INTO v_users;
-
- FOR i IN v_users.FIRST .. v_users.LAST LOOP
- PIPE ROW(v_users(i));
- END LOOP;
-
- RETURN;
-END;
-/
-</code>
-
-<code>
-ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD"SUPRISE!"';
-SELECT COLUMN_VALUE AS "REGULARS" FROM TABLE(users_since(sysdate - 30));
-
-v_query = SELECT username FROM users WHERE superuser = 0 and accessed_at >
-'2011-07-04SUPRISE!' order by accessed_at desc
-
-REGULARS
---------
-janihur
-albundy
-donduck
-
-ALTER SESSION SET NLS_DATE_FORMAT = '"'' or superuser = 1 or username = ''whocares"';
-SELECT COLUMN_VALUE AS "SUPERUSER IS" FROM TABLE(users_since(sysdate - 30));
-
-v_query = SELECT username FROM users WHERE superuser = 0 and accessed_at > ''
-or superuser = 1 or username = 'whocares' order by accessed_at desc
-
-SUPERUSE
---------
-petdance
-</code>
-
-The remedy is to set the format modifier explicitly: @to_char(p_since, 'YYYY-MM-DD')@.
-
-<code>
-ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
-
-CREATE OR REPLACE TYPE userlist_t AS TABLE OF VARCHAR2(8);
-/
-
-CREATE OR REPLACE FUNCTION users_since(
- p_since IN DATE
-) RETURN userlist_t PIPELINED AS
- v_users userlist_t;
- v_query constant varchar2(32767) :=
- 'SELECT username FROM users WHERE superuser = 0 and accessed_at > ''' || to_char(p_since, 'YYYY-MM-DD') || ''' order by accessed_at desc';
-BEGIN
- DBMS_OUTPUT.PUT_LINE('v_query = ' || v_query);
- EXECUTE IMMEDIATE v_query BULK COLLECT INTO v_users;
-
- FOR i IN v_users.FIRST .. v_users.LAST LOOP
- PIPE ROW(v_users(i));
- END LOOP;
-
- RETURN;
-END;
-/
-</code>
-
-Now the value of NLS parameter @NLS_DATE_FORMAT@ is ignored during the query.
-
-<code>
-ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD"SUPRISE!"';
-SELECT COLUMN_VALUE AS "REGULARS" FROM TABLE(users_since(sysdate - 30));
-
-v_query = SELECT username FROM users WHERE superuser = 0 and accessed_at >
-'2011-07-04' order by accessed_at desc
-
-REGULARS
---------
-janihur
-albundy
-donduck
-</code>
View
75 s/postgresql.md
@@ -0,0 +1,75 @@
+PostgreSQL
+==========
+
+All of PostgreSQL's [procedural languages](http://www.postgresql.org/docs/current/static/xplang.html), which allow you to write functions and procedures inside the database, allow you to execute arbitrary SQL statements.
+
+PL/pgSQL
+--------
+
+The safest way to execute SQL inside a PL/pgSQL statement is just to do so:
+
+ CREATE OR REPLACE FUNCTION user_access (
+ p_uname TEXT
+ ) RETURNS timestamp language plpgsql AS $$
+ BEGIN
+ RETURN accessed_at FROM users WHERE username = p_uname;
+ END;
+ $$;
+
+For such a simple case, you're actually better off writing a pure SQL function:
+
+ CREATE OR REPLACE FUNCTION user_access (
+ p_uname TEXT
+ ) RETURNS timestamp language sql AS $$
+ SELECT accessed_at FROM users WHERE username = $1;
+ $$;
+
+But sometimes you have to do more complicated things. Perhaps you dynamically add @WHERE@ clause expressions based on input. In such cases, you'll end up using PL/pgSQL's @EXECUTE@ syntax. Here's an example with an SQL injection vulnerability:
+
+ CREATE OR REPLACE FUNCTION get_users(
+ p_column TEXT,
+ p_value TEXT
+ ) RETURNS SETOF users LANGUAGE plpgsql AS $$
+ DECLARE
+ query TEXT := 'SELECT * FROM users';
+ BEGIN
+ IF p_column IS NOT NULL THEN
+ query := query || ' WHERE ' || p_column
+ || $_$ = '$_$ || p_value || $_$'$_$;
+ END IF;
+ RETURN QUERY EXECUTE query;
+ END;
+ $$;
+
+Both the @p_column@ and the @p_value@ arguments are vulnerable. The way to avoid this problem is to use the @quote_ident()@ function to quote an SQL identifier (@p_column@ in this case) and @quote_lteral()@ to quote a literal value:
+
+ CREATE OR REPLACE FUNCTION get_users(
+ p_column TEXT,
+ p_value TEXT
+ ) RETURNS SETOF users LANGUAGE plpgsql AS $$
+ DECLARE
+ query TEXT := 'SELECT * FROM users';
+ BEGIN
+ IF p_column IS NOT NULL THEN
+ query := query || ' WHERE ' || quote_ident(p_column)
+ || ' = ' || quote_literal(p_value);
+ END IF;
+ RETURN QUERY EXECUTE query;
+ END;
+ $$;
+
+It's quite a bit easier to read, too!
+
+PL/Perl
+-------
+
+TODO.
+
+PL/Python
+---------
+
+TODO.
+
+### PL/Tcl
+
+TODO.
View
71 s/postgresql.textile
@@ -1,71 +0,0 @@
-h1. PostgreSQL
-
-All of PostgreSQL's "procedural languages":http://www.postgresql.org/docs/current/static/xplang.html, which allow you to write functions and procedures inside the database, allow you to execute arbitrary SQL statements.
-
-h2. PL/pgSQL
-
-The safest way to execute SQL inside a PL/pgSQL statement is just to do so:
-
-bc. CREATE OR REPLACE FUNCTION user_access (
- p_uname TEXT
-) RETURNS timestamp language plpgsql AS $$
-BEGIN
- RETURN accessed_at FROM users WHERE username = p_uname;
-END;
-$$;
-
-For such a simple case, you're actually better off writing a pure SQL function:
-
-bc. CREATE OR REPLACE FUNCTION user_access (
- p_uname TEXT
-) RETURNS timestamp language sql AS $$
- SELECT accessed_at FROM users WHERE username = $1;
-$$;
-
-But sometimes you have to do more complicated things. Perhaps you dynamically add @WHERE@ clause expressions based on input. In such cases, you'll end up using PL/pgSQL's @EXECUTE@ syntax. Here's an example with an SQL injection vulnerability:
-
-bc. CREATE OR REPLACE FUNCTION get_users(
- p_column TEXT,
- p_value TEXT
-) RETURNS SETOF users LANGUAGE plpgsql AS $$
-DECLARE
- query TEXT := 'SELECT * FROM users';
-BEGIN
- IF p_column IS NOT NULL THEN
- query := query || ' WHERE ' || p_column
- || $_$ = '$_$ || p_value || $_$'$_$;
- END IF;
- RETURN QUERY EXECUTE query;
-END;
-$$;
-
-Both the @p_column@ and the @p_value@ arguments are vulnerable. The way to avoid this problem is to use the @quote_ident()@ function to quote an SQL identifier (@p_column@ in this case) and @quote_lteral()@ to quote a literal value:
-
-bc. CREATE OR REPLACE FUNCTION get_users(
- p_column TEXT,
- p_value TEXT
-) RETURNS SETOF users LANGUAGE plpgsql AS $$
-DECLARE
- query TEXT := 'SELECT * FROM users';
-BEGIN
- IF p_column IS NOT NULL THEN
- query := query || ' WHERE ' || quote_ident(p_column)
- || ' = ' || quote_literal(p_value);
- END IF;
- RETURN QUERY EXECUTE query;
-END;
-$$;
-
-It's quite a bit easier to read, too!
-
-h2. PL/Perl
-
-TODO.
-
-h2. PL/Python
-
-TODO.
-
-h3. PL/Tcl
-
-TODO.
View
17 s/python.md
@@ -0,0 +1,17 @@
+Python
+======
+
+Using the [Python DB API](http://wiki.python.org/moin/DatabaseProgramming/), don't do this:
+
+ # Do NOT do it this way.
+ cmd = "update people set name='%s' where id='%s'" % (name, id)
+ curs.execute(cmd)
+
+Instead, do this:
+
+ curs.execute('update people set name=:1 where id=:2', [name, id])
+
+To do
+-----
+
+- Add some narrative.
View
19 s/python.textile
@@ -1,19 +0,0 @@
-h1. Python
-
-Using the "Python DB API":http://wiki.python.org/moin/DatabaseProgramming/, don't do this:
-
-<code>
-# Do NOT do it this way.
-cmd = "update people set name='%s' where id='%s'" % (name, id)
-curs.execute(cmd)
-</code>
-
-Instead, do this:
-
-<code>
-curs.execute('update people set name=:1 where id=:2', [name, id])
-</code>
-
-h2. To do
-
-* Add some narrative.
View
18 s/ruby.md
@@ -0,0 +1,18 @@
+Ruby
+====
+
+In Ruby on Rails using [ActiveRecord](http://guides.rubyonrails.org/active_record_querying.html):
+
+ Person.find :all, :conditions => ['id = ? or name = ?', id, name]
+
+or
+
+ Person.find_by_sql ['SELECT * from persons WHERE name = ?', name]
+
+
+Using [Ruby/DBI](http://ruby-dbi.rubyforge.org/): analog to [Perl](./perl.html).
+
+To do
+-----
+
+- Add some narrative.
View
19 s/ruby.textile
@@ -1,19 +0,0 @@
-h1. Ruby
-
-In Ruby on Rails using "ActiveRecord":http://guides.rubyonrails.org/active_record_querying.html:
-
-<code>
-Person.find :all, :conditions => ['id = ? or name = ?', id, name]
-</code>
-
-or
-
-<code>
-Person.find_by_sql ['SELECT * from persons WHERE name = ?', name]
-</code>
-
-Using "Ruby/DBI":http://ruby-dbi.rubyforge.org/: analog to "Perl":./perl.html.
-
-h2. To do
-
-* Add some narrative.
View
18 s/scheme.md
@@ -0,0 +1,18 @@
+Scheme
+======
+
+CHICKEN Scheme
+--------------
+
+[CHICKEN](http://www.call-with-current-continuation.org/) is a compiler for [Scheme](http://schemers.org/)
+
+
+ (define DB (sqlite3:open "myexample.db"))
+ (sqlite3:exec DB "INSERT INTO Person(name) VALUES(?)" "Luke Skywalker")
+ (sqlite3:for-each-row print DB "SELECT id,name FROM Person WHERE id=?" 3)
+ (define adults (sqlite3:map-row DB "SELECT name FROM Person WHERE age >= ?" 18))
+
+To do
+-----
+
+- Add some narrative.
View
16 s/scheme.textile
@@ -1,16 +0,0 @@
-h1. Scheme
-
-h2. CHICKEN Scheme
-
-"CHICKEN":http://www.call-with-current-continuation.org/ is a compiler for "Scheme":http://schemers.org/
-
-<code>
-(define DB (sqlite3:open "myexample.db"))
-(sqlite3:exec DB "INSERT INTO Person(name) VALUES(?)" "Luke Skywalker")
-(sqlite3:for-each-row print DB "SELECT id,name FROM Person WHERE id=?" 3)
-(define adults (sqlite3:map-row DB "SELECT name FROM Person WHERE age >= ?" 18))
-</code>
-
-h2. To do
-
-* Add some narrative.
View
1 tt/footer.tt
@@ -1,6 +1,7 @@
<div id="footer">
<a href="/de_DE" xml:lang="de-DE" lang="de-DE" hreflang="de-DE">deutsch</a>
<a href="/" xml:lang="en" lang="en" hreflang="en">English</a>
+<p>This site's content is available under the <a href="https://creativecommons.org/licenses/by-sa/3.0/">Creative Commons Attribution-ShareAlike 3.0 License</a>.</p>
</div>
<a href="http://github.com/petdance/bobby-tables"><img
src="http://s3.amazonaws.com/github/ribbons/forkme_right_gray_6d6d6d.png" alt="[% |loc %]Fork me on GitHub[% END %]" /></a>

0 comments on commit f3e3e40

Please sign in to comment.
Something went wrong with that request. Please try again.