Problems with sql_escape_percent function

[Zeratul]

Function sql_escape_percent(sql) from module trac/db/util.py is escaping quite too many characters. It's automatically used when executing sql statements with cursor.execute() and it replaces every '%' (percent sign) with '%%'. But '%' is sql special sign used in 'like' clauses - for example:

"SELECT oldvalue FROM ticket_change WHERE filed = 'comment' AND 
oldvalue LIKE '%.comment_number OR oldvalue = comment_number"

which may be useful for getting comment from database by comment_number. When above statement is executed via

cursor.execute("SELECT oldvalue FROM ticket_change WHERE filed =
 'comment' AND oldvalue LIKE '%.comment_number OR oldvalue = 
 comment_number")

the percent sign is doubled so the really executed query is:

"SELECT oldvalue FROM ticket_change WHERE filed = 'comment' AND 
oldvalue LIKE '%%.comment_number OR oldvalue = comment_number"

which theoretically gives the same results but is somehow strange…

[ebray]

That's what db.like() and db.like_escape() are for (besides that fact of different DBMS having subtle differences in LIKE). For example:

cursor.execute("SELECT name, author FROM wiki "
               "WHERE name " + db.like() +
               "GROUP BY name ORDER BY name, version",
               (db.like_escape(prefix) + '%',))

[Christian Boos]

made description readable