CODEWITHSUNDEEP
phpsqldb2
Stephen Adipradhana
Stephen Adipradhana

Reputation: 147

DB2 error Improper use of a string column, host variable, constant, or function

I'm trying to search all tables and columns for a certain value and I'm using php connection to IBM DB2 database. I was wondering if the error is cause because of multiple usage of union.

This is my error message :

Improper use of a string column, host variable, constant, or function "NAMABARANG". SQLSTATE=42907 SQLCODE=-134

And here is my code : 

$keyword=$_GET['keyword'];
$query="SELECT * FROM INVENTORY WHERE NAMABARANG LIKE '%".$keyword."%'".
    " UNION ALL SELECT * FROM INVENTORY WHERE ARRIVALDATE LIKE '%".$keyword."%'".
     " UNION ALL SELECT * FROM INVENTORY WHERE PAPERNUMBER LIKE '%".$keyword."%'".
     " UNION ALL SELECT * FROM INVENTORY WHERE SERIALNUMBER LIKE '%".$keyword."%'".
     " UNION SELECT * FROM INVENTORY WHERE CONDITION LIKE '%".$keyword."%'".
     " UNION ALL SELECT * FROM INVENTORY WHERE LOCATION LIKE '%".$keyword."%'".
     " UNION ALL SELECT * FROM INVENTORY WHERE CONFIRMATIONDATE LIKE '%".$keyword."%'".
     " UNION ALL SELECT * FROM INVENTORY WHERE BARCODE LIKE '%".$keyword."%'".
     " UNION ALL SELECT * FROM USERANDPASSWORD WHERE USERNAME LIKE '%".$keyword."%'";
     " UNION ALL SELECT * FROM USERANDPASSWORD WHERE ACCESS LIKE '%".$keyword."%'";

Upvotes: 3

Views: 10408

Answers (1)

user1919238
user1919238

Reputation:

See info on that error message here. The problem appears to be that NAMABARANG is one of the long character/clob types, and it is illegal to have that in a query that performs grouping. This query performs grouping because one of the sub-queries uses UNION rather than UNION ALL. UNION groups when it removes duplicate rows.

A query like this should probably not return all columns (SELECT *). Instead, only return what you really need. Possibly an ID column that can identify each row that matched, and the name of the column and table that matched. This will remove the error.

A few other points:

  • Your code is very unsafe! You are taking a parameter directly from a form and inserting it into SQL. Someone can use this to do all sorts of evil things, even delete tables from your database. Please take a look at the first answer to this question for the safe way of using user-entered data in queries.
  • You are unioning rows from two different tables Does USERANDPASSWORD have the exact same columns as INVENTORY? If not, this union will not work. Solves this by not selecting *, instead select a similar set of columns from each table.
  • UNION or UNION ALL? Do you want to return multiple matches from a single row if more than one column contains the search string? If so, use UNION ALL. Otherwise, use UNION to remove duplicates.

Upvotes: 3

Related Questions