Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

#907 Error while editing some fields in Squirrel

3.1
closed-fixed
5
2011-03-15
2010-10-16
Anonymous
No

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

To reproduce the bug:
1) Create test table
CREATE TABLE test AS SELECT '&test'

2) Select values from the table
SELECT * FROM test

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

Editing is impossible and leads to an error

Discussion

  • Hi,
    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.

    Greeting...
    Stefan
    then you have no problems.

     
  • 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.

    Stefan

     

  • 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.

    Rob

     

  • 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
  • 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.

    Stefan

     
  • 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.

    Rob

     
  • Fixed in SVN

    Stefan

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