inserting a string to a prepared statement without ' '

Question

I have the following sql statement:

Select i.imageID, theImage, translationRating
From images i
inner join translationchains  t
on t.imageId = i.imageid
where i.userID=(someUserID) And i.translated =0 
and t.targetLang in (select targetLang from translationChains)

I want to make it a prepared statement to use in my java code:

Select i.imageID, theImage, translationRating
From images i
inner join translationchains  t
on t.imageId = i.imageid
where i.userID=? And i.translated =0 
and t.targetLang in (select ? from translationChains)

the input for the first ? is a user Id (integer) and it's working fine.

the second input is a string which containing a languageId - it's a string that either contain a number that represent a language, or the string "targetLang" which is the name of the column (=all langs)

in my java code I did the following:

Image.setInt(1, userID);
Image.setString(2, langID);
Image.executeQuery()

my problem is when I'm sending the string "targetLang" as the second parameter the prepared statement is inserting it as 'targetLang' (with ' before and after'), with numbers it's not a problem beacuse 3='3', but with strings it is giving me different results from what I need - I always get an empty result set because nothing is equal to 'targetLang'. I need to insert this string to the prepared statement without the '. is it possible or I need to use something differ from prepared statement?

I know I can build a string that contain all this query but I'm looking for something more elegant tnx

---

edit:

This is the Create table translationChains:

Create Table if not exists TranslationChains (
  ImageID int (10) NOT NULL,  
  SourceLang int NOT NULL,
  TargetLang int NOT NULL,
  Translated tinyint default 0,
  Translation text,
  Translator varchar (30),
  CONSTRAINT translate_image PRIMARY KEY (ImageID,SourceLang, TargetLang),
  FOREIGN KEY (ImageID) REFERENCES Images(ImageID) ON DELETE CASCADE,
  FOREIGN KEY (SourceLang) REFERENCES Languages(languageID) ON DELETE CASCADE,
  FOREIGN KEY (TargetLang) REFERENCES Languages(languageID) ON DELETE CASCADE)

As you can see, I am trying to take images according to the "targetLang" column which is an int column. Each number represent a language.

Now I have two options in selecting images from that table:

• Choosing a specific language, i.e. giving a number as the second input.
• Choosing all languages, i.e. setting the second input to be the column name "targetLang".

So I am not comparing to the fixed string "targetLang". I want to choose all values possible for this column (select targetLang from translationChains).

Answer 1

Query parameters can take the place of a literal value only -- i.e. where you would normally put a quoted string literal, a quoted date literal, or a numeric literal. Thus a string value will always be interpreted as a string literal, as if you had put it into the query with single-quotes.

For column names, table names, SQL expressions, SQL keywords, etc., you have to interpolate these values into the SQL query prior to the call to prepare().

To be safest, use whitelisting so that user input is never interpolated into SQL queries verbatim. Always validate the user input (or any other content) before using it in a query.

I have written many examples of whitelisting when building an SQL query.

You can also see examples in my presentation SQL Injection Myths and Fallacies and my book SQL Antipatterns: Avoiding the Pitfalls of Database Programming.

---

Re your comment:

I'm not sure exactly what you are doing. It sounds like you either want t.targetLang to equal to a literal number, or a fixed string 'targetLang'. If so, I don't know why you're doing a subquery at all -- you should just use a query parameter for a value:

where i.userID = ? And i.translated = 0 
and t.targetLang = ?

Image.setString(2, langID); // either '3' or 'targetLang'

But I'm not sure I understand your description fully. Does the number represent the position of a column you want to compare against? On the same row as t.targetLang? If so, I still don't think you need a subquery. You could use an expression like the following:

where i.userID = ? And i.translated = 0 
and FIELD(t.targetLang, t.targetLang, t.column2, t.column3, t.column4) = ?

Image.setString(2, '1'); // for the case where you allow all langs
Image.setString(2, '3'); // for the case where you want to match a specific column.

See the manual on the FIELD() function in MySQL, which searches for the first argument among a list of expressions. It returns the integer position of the field that matches.

Using this method, you pass the position you want your t.targetLang to match, and this allows you to pass the dynamic part as a value, instead of as a column name. It also allows you to avoid the subquery.

If I've still got it wrong and don't understand your problem, please edit your original question and provide more detail. SHOW CREATE TABLE translationChains would help. Also can you answer some of things I had to make assumptions about, e.g. are you trying to match t.targetLang against a value in another column on the same row?

---

Okay, now I understand your goal better. Here's a query that does what you want:

SELECT i.imageID, theImage, translationRating FROM Images i INNER JOIN TranslationChains t ON t.imageId = i.imageid WHERE i.userID = ? AND i.translated = 0 AND ? IN (t.targetLang, 'targetLang')

You can pass as the second parameter either an integer to match against the t.targetLang, or else the literal string 'targetLang' which will match against the value 'targetLang' in the predicate.

I can't recommend this solution, though, since it will probably not use indexes very well, it may have to perform type conversions by comparing the string parameter to the integer column.

The better practice when you want to match all languages is to execute the query without the last term in the WHERE clause. That is, in your application, append that term conditionally, only when you want the query to fetch a specific language. Otherwise, omit that term and skip the Image.setString().

String sql = "SELECT i.imageID, theImage, translationRating
FROM Images i
INNER JOIN TranslationChains t
  ON t.imageId = i.imageid
WHERE i.userID = ? AND i.translated = 0 ";

if (langId) {
  sql += " AND ? IN (t.targetLang, 'targetLang')";
}

. . .

Image.setInt(1, userID);
if (langId) {
  Image.setString(2, langID);
}
Image.executeQuery()