Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
[Solved] SQL Error uploading files with apostrophes in names.
Warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘s Theory and Practice of Nonviolence.pdf’, category = ‘’, permissions = ‘ at line 1 insert into txp_file set filename = ‘ISPN 2Day Training-King’s Theory and Practice of Nonviolence.pdf’, category = ‘’, permissions = ‘’, description = ‘’, size = ‘596231’, created = now(), modified = now(), author = ‘adam’ textpattern/include/txp_file.php:547 safe_insert() in /home/public/textpattern/lib/txplib_db.php on line 85
The problem is that apostrophe in the file name. While there’s an easy enough workaround, I have a feeling that my suggestion to rename files to something reasonable before uploading them won’t resonate with my users. This error also implies that filenames aren’t being properly escaped before being inserted into the database, which could be a bigger issue.
Offline
Re: [Solved] SQL Error uploading files with apostrophes in names.
I cannot reproduce this on a vanilla Textpattern 4.2.0 site. Please post you diagnostics.
Offline
Re: [Solved] SQL Error uploading files with apostrophes in names.
I’ve gotten the same results on two installs on two (very) different servers.
Here’s site #1:
Textpattern version: 4.2.0 (r3275)
Last Update: 2009-09-20 01:49:26/2009-08-28 19:31:19
Document root: /home/public
$path_to_site: /home/public
Textpattern path: /home/public/textpattern
Permanent link mode: section_id_title
upload_tmp_dir: /tmp
Temporary directory path: /f1/content/testpattern/public/textpattern/tmp
Site URL: testpattern.adamtbradley.com
PHP version: 5.2.9
GD Image Library: version bundled (2.0.34 compatible), supported formats: GIF, JPG, PNG
Server TZ: UTC
Server Local Time: 2009-11-16 18:21:06
DST enabled?: 1
Automatically adjust DST setting?: 0
Time Zone: (-18000)
MySQL: 5.0.77
Locale: en_US.UTF-8
Server: Apache/x.x
PHP Server API: cgi
RFC 2616 headers: 0
Server OS: FreeBSD 7.2-STABLE
Active plugins: ajw_comments_feed-0.6, wet_for_each_image-0.5, rss_auto_excerpt-0.5, ied_plugin_composer-0.4, fpx_image_import-0.4, upm_img_popper-1.3.7, atb_musicplayer-0.1m
Admin-side theme: remora 4.2.0
Pre-flight check:
------------------------
Temporary directory path is not writable: /f1/content/testpattern/public/textpattern/tmp
------------------------
.htaccess file contents:
------------------------
#DirectoryIndex index.php index.html
#Options +FollowSymLinks
#Options -Indexes
<IfModule mod_rewrite.c>
RewriteEngine On
#RewriteBase /relative/web/path/
RewriteCond %{REQUEST_FILENAME} -f [OR]
RewriteCond %{REQUEST_FILENAME} -d
RewriteRule ^(.+) - [PT,L]
RewriteCond %{REQUEST_URI} !=/favicon.ico
RewriteRule ^(.*) index.php
RewriteCond %{HTTP:Authorization} !^$
RewriteRule .* - [E=REMOTE_USER:%{HTTP:Authorization}]
</IfModule>
#php_value register_globals 0
------------------------And here’s #2 (those two files were modified to make Textpattern use my employer’s single sign-on system instead of its own authentication):
Textpattern version: 4.2.0 (r3275)
Last Update: 2009-11-12 02:48:08/2009-08-28 17:31:20
Document root: /www/data/httpd/htdocs/Departments/Swearer_Center
$path_to_site: /www/data/httpd/htdocs/Departments/Swearer_Center/sii
Textpattern path: /www/data/httpd/htdocs/Departments/Swearer_Center/sii/textpattern
Permanent link mode: section_id_title
upload_tmp_dir: /tmp
Temporary directory path: /www/data/httpd/htdocs/Departments/Swearer_Center/sii/txp/textpattern/tmp
Site URL: swearercenter.brown.edu/sii
PHP version: 5.2.1
Register globals: 1
GD Image Library: version bundled (2.0.28 compatible), supported formats: GIF, JPG, PNG
Server TZ: America/New_York
Server Local Time: 2009-11-16 13:24:47
DST enabled?: 0
Automatically adjust DST setting?: 0
Time Zone: America/New_York (-18000)
MySQL: 4.1.22-log
Locale: English_United States.1252
Server: Apache/1.3.37 (Unix) PHP/5.2.1 mod_perl/1.29 mod_ssl/2.8.28 OpenSSL/0.9.8d
Apache version: Apache/1.3.37 (Unix) PHP/5.2.1 mod_perl/1.29 mod_ssl/2.8.28 OpenSSL/0.9.8d
PHP Server API: apache
RFC 2616 headers:
Server OS: Linux 2.6.18-92.1.17.el5
Active plugins: wet_for_each_image-0.5, atb_section_hierarchy-0.1m, glx_gravatar-2009.3, jmd_author-0.1, rss_auto_excerpt-0.5, zem_nth-0.1, atb_swearer_client-0.1m, smd_query-0.21, atb_choose_search_page-0.1, upm_image-0.6.2, hak_tinymce-0.7.4, rss_unlimited_categories-0.7.4, tru_tags-3.5, stm_javascript-0.3m, aks_rss-0.1.4m, ied_hide_in_admin-0.1.6, sed_packed_custom_fields-0.3.108m, sed_plugin_library-0.5.2
Admin-side theme: SeaGloss 0.1
Pre-flight check:
------------------------
Some Textpattern files have been modified:
/www/data/httpd/htdocs/Departments/Swearer_Center/sii/textpattern/include/txp_admin.php,
/www/data/httpd/htdocs/Departments/Swearer_Center/sii/textpattern/include/txp_auth.php
The following PHP functions (which may be necessary to run Textpattern) are disabled on your server: show_source, escapeshellcmd, escapeshellarg, disk_free_space, diskfreespace, set_time_limit, leak
Clean URL test failed.
------------------------
.htaccess file contents:
------------------------
#DirectoryIndex index.php index.html
Options +FollowSymLinks
#Options -Indexes
<IfModule mod_rewrite.c>
RewriteEngine On
#RewriteBase /relative/web/path/
RewriteCond %{REQUEST_FILENAME} -f [OR]
RewriteCond %{REQUEST_FILENAME} -d
RewriteRule ^(.+) - [PT,L]
RewriteCond %{REQUEST_URI} !=/favicon.ico
RewriteRule ^(.*) index.php
RewriteCond %{HTTP:Authorization} !^$
RewriteRule .* - [E=REMOTE_USER:%{HTTP:Authorization}]
</IfModule>
#php_value register_globals 0
------------------------A second attempt on #1 after disabling all plugins gives me the same result.
Thanks,
Adam
Offline
Re: [Solved] SQL Error uploading files with apostrophes in names.
Just to cover we are on common grounds here: This happens when you:
- enter the “Files” tab and
- upload a new file with a single quote embedded in its file name?
Offline
Re: [Solved] SQL Error uploading files with apostrophes in names.
Just to cover we are on common grounds here: This happens when you:
- enter the “Files” tab and
- upload a new file with a single quote embedded in its file name?
Right.
Offline
Re: [Solved] SQL Error uploading files with apostrophes in names.
I just tried it and got the same error
Warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘paper.pdf’, category = ‘’, permissions = ‘’, description = ‘’, s’ at line 1 insert into txp_file set filename = ‘graph’paper.pdf’, category = ‘’, permissions = ‘’, description = ‘’, size = ‘7065’, created = now(), modified = now(), author = ‘matt’
I’m on Textpattern version: 4.2.0 (r3287)
MySQL: 5.0.81-community-log
Last edited by MattD (2009-11-16 19:52:03)
Piwik Dashboard, Google Analytics Dashboard, Minibar, Article Image Colorpicker, Admin Datepicker, Admin Google Map, Admin Colorpicker
Offline
Re: [Solved] SQL Error uploading files with apostrophes in names.
wet wrote:
Just to cover we are on common grounds here: This happens when you:
Yes, it definetly happens. $newname is unescaped when it’s been inserted into DB. Only thing that is been done to it, is regular expression of sanitizeForFile().
txp_file.php @ line 621 from:
$id = file_db_add($newname,$category,$permissions,$description,$size);To:
$id = file_db_add(doSlash($newname),$category,$permissions,$description,$size);Also it would be good to add /[^A-Za-z0-9\-]/ cleaning regexs and dumbdown() to sanitizeForFile() to avoid strange URLs. Or preferably other field to store the URL title.
Offline
Re: [Solved] SQL Error uploading files with apostrophes in names.
Gocom wrote:
Or preferably other field to store the URL title.
I concur we need a Title field for files. I did it on an install once and it’s darn useful. When I get a moment I’ll try and check what I did was robust enough for core use.
The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.
Txp Builders – finely-crafted code, design and Txp
Offline
Re: [Solved] SQL Error uploading files with apostrophes in names.
Thanks for the report. Fixed in r3288. File title goodness left for Mr Bloke to build, though.
Offline