Geek asks and answers

Insert a line into a prepared statement without ''

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 this a ready-made expression for 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)

entry for the first? is the user identifier (integer) and works fine.

the second input is a string that contains the language; id is a string that either contains a number representing the 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()

, "targetLang" "targetLang" ( " " ), , , beacuse 3 = '3', , - , "targetLang" . '. - ?

, , , - TNX

:

: 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)

, "targetLang" , int. .

:

  • , .. .
  • , .. "targetLang" .

"targetLang" . ( targetLang from translationChains).

+5
2

- .. , . , , .

, , SQL, SQL .. SQL- prepare().

, , SQL- . ( ) .

SQL-.

SQL Injection Myths and Fallacies SQL Antipatterns: .

:

, . , , t.targetLang 'targetLang'. , , - :

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

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

, . , ? , t.targetLang? , , . , :

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.

. FIELD() MySQL, . , .

, , , t.targetLang , , . .

, . SHOW CREATE TABLE translationChains . , , , t.targetLang ?

, . , , :

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

, t.targetLang, targetLang, 'targetLang ' .

, , , , , , , .

, , WHERE. , , , . 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()
+3

:

t.targetLang in (select? from translationChains)

To:

and (
        (
            ? = 'targetLang' and
            t.targetLang in (
                                    select
                                        targetLang
                                    from
                                        translationChains
                            )
        )
        or
        ? = t.targetLang
    )

setString (3, langId), .. , . , , , -, !

0

More articles:


All Articles