sanitizing sql with sqlalchemy

[alonn]

I'm using sqlalchemy orm (with turbogears) to write data from a web application to an mssql 2005 Db (used by another application, not maintained by me).

after dealing with a serious case of data corruption (basically because of user data including the "." sign). is there a way to use sqlalchemy also as a validator/sanitizor for userdate?

I know there is a basic sql escaping (preventing sql injection) baked into sqlalchemy but obviousely I need something stronger.

if sqlalchemy can't handle it by itself is there another library (or sqlalchemy plugin) that can give me this functionality?

thanks for the help 

[Lloyd Kvam]

I should probably remove my fingers from the keyboard since I have so little experience with mssql, but here goes:

Isn't the "." only special in field and table names?

If that's where the user input was used, I think it's the programmer's responsibility to validate/sanitize the input.  A plugin simply doesn't have the knowledge required to sanitize those parts of an SQL statement.

Please correct me if I'm wrong.

[Werner]

On 31/12/2012 23:24, alonn wrote:
> I'm using sqlalchemy orm (with turbogears) to write data from a web
> application to an mssql 2005 Db (used by another application, not
> maintained by me).
> after dealing with a serious case of data corruption (basically
> because of user data including the "." sign).

Can you give more detail on how a "." (point/full stop) in user data
corrupted your database.

A point is valid data in lots of situations, so should not cause you
problems.

Werner

[alonn]

Actually I don't know what's causing the corruption but the "."  looks like the only unvalid one in a varchar field.

since after the insert the table just stopped working (not responding to SELECT or DELETE) while the rest of the tables works fine

so I'm looking for something like html markupsafe library 

[Werner]

On 01/01/2013 19:34, alonn wrote:
> Actually I don't know what's causing the corruption but the "." looks
> like the only unvalid one in a varchar field.

Why would a "." in a varchar field not be valid? Just consider
something like "Firstname MidInitial. LastName", why would that not be
valid in a varchar column?

I am pretty sure that the "." is not your problem.

Provide more details and hopefully someone can help you identify your
real problem.

Werner

[Tomas Vondra]

My bet is that when OP talks about "data corruption", he actually does
not mean that the database crashed and the data files are somehow damaged.

I believe the issue is that the application assummes that the varchar
column does not contain some characters (e.g. a dot) yet the users
managed to get around the application-level checks and inserted such
values into the database. So the data are invalid from the application
point of view, but the database is perfectly healthy.

But that's something the OP needs to explain.

Tomas

[alonn]

This is what I thought. that the problem is with the application and not the sql server. unfortunately I try to access the table directly (either through sqlalchemy or directly from mssql management GUI) and both fail. the table just doesn't respond to SELECT, DELETE, TRUNCATE etc