How to (safely) embed JSON in a script tag

2010-12-25T05:48:00.001-08:00

If you embed JSON in a web page, you must make sure you are not vulnerable to XSS (Cross-site scripting) attacks. Usually you would just HTML escape your content, but that corrupts your JSON.

instead escape the script start and end characters and the ampersand character with their unicode encodings. This will prevent XSS but still allow for valid JSON

json += json.replace('&', '\\u0026') json += json.replace('<', '\\u003c') json += json.replace('>', '\\u003e')

If you use simplejson for generating JSON, the newest version has a JSONEncoderForHTML that does exactly this.

Posted via email from Yet another blog...

Migrating trac from SQLite to MySQL

2009-11-07T12:29:00.001-08:00

A little while ago I was tasked with migrating about hundred trac instances from SQLite to MySQL. Unfortunately SQLites schema definitions is not compatible with MySQL. So I had to wrote some scripts to handle it. After a little bit of experimenting everything worked perfectly.

The Strategy I employed was like this:

Make a dump of a default MySQL trac db schema.
Make a dump of the trac SQLite database
Remove db schema definitions from the SQLite dump
Concatenate the MySQL and SQLite dumps
Load data into MySQL
Edit Trac's database settings to use MySQL
Do a trac-admin upgrade

MYSQL_USER=username
MYSQL_PSWD=password

SQLITECMD=sqlite3
TRACBASE=/path/to/tracreps

# loop through all trac instances in tracreps
for d in $( ls $TRACBASE )
do
if [ -d "$TRACBASE/$d" ]; then
echo $d
TRACNAME=$d

# create database in mysql
echo "creating database for $TRACNAME..."
mysqladmin --user $MYSQL_USER -p$MYSQL_PSWD create $TRACNAME

# dump sqlite db structure + data
echo "dumping data from sqlite..."
$SQLITECMD $TRACBASE/$TRACNAME/db/trac.db .dump > trac.sqlite.sql

# remove database definitions from dump using a custom python script
echo "cleaning database definition from dump..."
`./cleansql.py < trac.sqlite.sql > trac.sqlite.sql.dataonly`

# concatenate mysql database definitions and sqlite data
cat trac.mysql.sql trac.sqlite.sql.dataonly > trac.sql

echo "loading data into mysql..."
mysql --user $MYSQL_USER -p$MYSQL_PSWD --default_character_set utf8 $TRACNAME < trac.sql

# edit database connection string in trac.ini
sed -i "s?sqlite:db/trac.db?mysql://$MYSQL_USER:$MYSQL_PSWD@localhost:3306/$TRACNAME?" $TRACBASE/$TRACNAME/conf/trac.ini

echo "upgrading mysql database..."
trac-admin $TRACBASE/$TRACNAME upgrade --no-backup

To remove schema definitions from the sqlite dump and fix some incompatibilities I made this python script referenced above as cleansql.py:

#!/usr/bin/env python

import sys
import re

file = sys.stdin.read()
file = re.sub(r'(CREATE (TABLE|INDEX)[^;]*|COMMIT|BEGIN TRANSACTION);', '', file)
file = re.sub(r'INSERT INTO "([^"]+)"', lambda m: 'INSERT INTO `%s`' % m.groups(1), file)
# fix sql for reports
file = re.sub(r'CAST$(.+) AS int$', lambda m: 'CAST(%s AS signed)' % m.groups(1), file)

sys.stdout.write(file)

And that's it. I hope this will benefit someone tasked with the same job.

Posted via web from Yet another blog...

Widget test

2008-12-01T15:14:00.000-08:00

Niels Sandholt Busch blog...

How to (safely) embed JSON in a script tag

Migrating trac from SQLite to MySQL

Widget test