#907 Error while editing some fields in Squirrel


Squirrel fails when user tries to edit DB fields which have particular characters.

To reproduce the bug:
1) Create test table

2) Select values from the table

3) Try to edit fields
right click-> Make Editable

Editing is impossible and leads to an error


  • Stefan Willinger

    It looks like the Exception is thrown from the JDBC-Driver

    2010-10-19 19:28:03,083 [AWT-EventQueue-1] ERROR net.sourceforge.squirrel_sql.client.session.DataSetUpdateableTableModelImpl - Exception seen during check on DB. Exception was:
    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 '&test='&test'' at line 1
    Update is probably not safe to do.
    Do you wish to proceed?
    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 '&test='&test'' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.Util.getInstance(Util.java:381)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1031)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2642)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2571)
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1464)

    In my opinion, the problem is, that your colum name is &test, which is irregular. You cannot create this table with a CREATE TABLE test (&test varchar(10)) DDL-Statement.
    If you would use an alias like
    CREATE TABLE test AS SELECT '&test' as col_1
    you will have no problems.

    By the way, I was not able to use the column &test at the command line of an MySQL-Database, so i don't think that this is an error from SQuirrel.

    then you have no problems.

  • Stefan Willinger

    After doing some research, I learned, that following would work:
    SELECT * FROM test a where `&test`= 'abc';
    UPDATE test set `&test`= 'abc' where `&test`= '&test';

    The trick is, that you could must quote such funky column names with an ` (back tick)
    See: http://www.perlmonks.org/?node_id=853593

    But what should Squirrel do? In my opinion you should avoid such funky column names.


  • Comment has been marked as spam. 

    You can see all pending comments posted by this user  here

    Anonymous - 2010-10-20

    wis775 :
    The problem is not the column name but the contents of the stored value.
    So if you have the data below:
    Table "test"
    id | sampleText
    1 | &test

    You won't be able to change the contents ('&test')

  • Rob Manning

    Rob Manning - 2010-10-20

    No, Stefan is correct here. Your original create statement creates a column with the name '&test'. Of course, a field may have the value '&test' and it can be edited without trouble if the column name doesn't involve special characters. See for yourself:

    drop table test;

    CREATE TABLE test ( id integer, sampleText varchar(10));

    INSERT INTO test values (1, '&test');

    SELECT * FROM test;

    (Make editable and change the 'sampleText' column value to whatever you like).

    So, this bug report - if it is at all valid - concerns the use if '&' (and probably many other special characters) in column names.

    If the trouble for you comes from editing a column named 'sampleText' with a value of '&test', then this bug is not reproducible.


  • Comment has been marked as spam. 

    You can see all pending comments posted by this user  here

    Anonymous - 2010-10-20

    Correct reproduction sql's:

    CREATE TABLE test222 (id mediumint(7) unsigned NOT NULL, testName varchar(1200) NOT NULL default '') ENGINE=InnoDB DEFAULT CHARSET=utf8

    insert into test222 values (2,'test & test\\');

    select * from test222

    Make editable and try to edit and save 'test & test'

    This leads to an exception

    Last edit: Anonymous 2013-10-24
  • Stefan Willinger

    The cause of the error is the backslash. At an MySql Database the backslash must be escaped with an double backslash. Squirrel doesn't do that.
    It seems like Squirrel needs an aditional DatabaseSpecificEscape like PostgreSQLEscape and MckoiSQLEscape.

    I have created an MySqlSQLEscape, but need some testing.


  • Rob Manning

    Rob Manning - 2010-10-21

    Wow... that's a bait-and-switch; but... OK.

    Stefan - submit a patch and I'll review it.


  • Stefan Willinger

    Fixed in SVN


  • Stefan Willinger

    • assigned_to: nobody --> wis775
    • status: open --> closed-fixed

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.

No, thanks