Geek asks and answers

Implementing a “Customizable” Connector System Safely

Background

Hello, I am developing an experimental / educational tool in PHP and MySQL. I am new to SQL, but I want to do everything right from the start. I use prepared PDO statements for all variable substitutions and wherever possible (therefore, as far as I understand, it will not be portable for databases other than MySQL). As for my problem, I have an idea on how to move on, but it will take me several hours to implement (I am new to SQL syntax), so I thought that I would first create a question if someone could scream: "This is not the way to do it!" and save me strength.

Problem

I would like to create an interface in which the user will choose from the drop-down menus:

  • table A,
  • one or more fields in this table, for example. A.xand A.y,
  • table B,
  • one or more fields in this table, for example. B.zand B.y,

and after presentation, the code will perform an internal join corresponding to each field, respectively, for example. A.x = B.z, A.y = B.yetc. and return all matched rows.

My plan is to generate an INNER JOINSQL statement, scroll through fields and insert placeholders ( ?), bind the appropriate parameters, and finally execute the statement.

Is there an easier way to do this? Is there a better way to do this? Will it be used in any way?

Thanks in advance. If no one answers by the time I finish (doubtful), I will post my decision.

Miscellaneous.

Suppose i check

  • A B,
  • ,
  • .

: . ( , , , !)

, , . , "", , .

+5
2

, , , - !:)

mdash, SQL, . A.x, B.z .. JOIN : , , SQL.

. - :

  • , SQL:

    <select name="join_a">
  <option value="1">x</option>
  <option value="2">y</option>
</select>
<select name="join_b">
  <option value="1">z</option>
  <option value="2">y</option>
</select>

    :

    switch ($_POST['join_a']) {
  case 1:  $acol = 'x'; break;
  case 2:  $acol = 'y'; break;
  default: die('Invalid input');
}
switch ($_POST['join_b']) {
  case 1:  $bcol = 'z'; break;
  case 2:  $bcol = 'y'; break;
  default: die('Invalid input');
}

$sql .= "FROM A JOIN B ON A.$acol = B.$bcol";

    , , PHP ( , SQL-), SQL .

  • , :

    <select name="join_a">
  <option>x</option>
  <option>y</option>
</select>
<select name="join_b">
  <option>z</option>
  <option>y</option>
</select>

    :

    if (!in_array($_POST['join_a'], ['x', 'y'])
 or !in_array($_POST['join_b'], ['z', 'y']))
   die('Invalid input');

$sql .= "FROM A JOIN B ON A.$_POST[join_a] = B.$_POST[join_b]";

    PHP in_array ( , , , , ).

  • , :

    mb_regex_encoding($charset); // charset of database connection
$sql .= 'FROM A JOIN B ON A.`' . mb_ereg_replace('`', '``', $_POST['join_a']) . '`'
                    . ' = B.`' . mb_ereg_replace('`', '``', $_POST['join_b']) . '`'

    , ( PHP mb_ereg_replace, MySQL ).

    , , SQL .

+2

, ( ), ; :)

, , , . , ( ) , , .

, ; , ; -, , :)

+1

More articles:


All Articles