Concat function in the Ajax response Php file breaks the script

Question

I am trying to concatentate two variables in the php response file of an Ajax Script.

The Response file is the following:

$company_id=$_POST['company']:
$query="SELECT `contact_company`.`contact_id`, CONCAT(`surname`,' '.`fname`), `contact_company`.`company_id` FROM `seta`.`contact_company` AS `contact_company`, `seta`.`contacts` AS `contacts` WHERE `contact_company`.`contact_id` = `contacts`.`contact_id` AND `contact_company`.`company_id` ='".$company_id."' ";

$result=mysqli_query($connection,$query) or die ("Query to get data from contact_company table failed: ".mysqli_connect_error());


while ($row=mysqli_fetch_array($result)) {
    $contact_id=$row["contact_id"];
    $company_id=$row["company_id"];
    $contact_name=$row["CONCAT(`surname`,' ',`fname`)"];

    echo "<option value=\"$contact_id\">
         $contact_name
         </option>";
    }

While this syntax of CONCAT works perfectly in the other php files, it doesn't here. Can anybody please advise? Thanks Regards

Tommaso

Little hint if you use single qoutes with echo you won't have to escape the double quotes ... `echo ' — Raymond Nijland, May 21 '18 at 15:17
The variable `$company_id` is prone to SQL injection attacks. Read this https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php to prevent SQL injections — Raymond Nijland, May 21 '18 at 15:19
@AjAX. I see that makes sense now, but I am a bit confused, how would you solve the issue? — Tommaso Lazzari, May 21 '18 at 15:21
@RaymondNijland that variable comes from a Dropdown selection, so I guess no code can be injected — Tommaso Lazzari, May 21 '18 at 15:22
"RaymondNijland that variable comes from a Dropdown selection, so I guess no code can be injected ." Every $_GET, $_POST variables can not be trusted and can contain SQL injection attack vectors or other attack vectors — Raymond Nijland, May 21 '18 at 15:32
"@RaymondNijland ..... but also, the only suggestion you have given, to replace " with ' and remove the escaping, breaks the code. Thanks for stopping this " Really because `echo '';` is valid PHP code and will not break the code.. Check this http://sandbox.onlinephpfunctions.com/code/78b02f87b0346e8867d339f9ef479bcf8b762d3d — Raymond Nijland, May 21 '18 at 15:46
I don't know why the ";" looks like a ":" but in the code it is ; — Tommaso Lazzari, May 23 '18 at 04:19

Karlo Kokkak · Accepted Answer · 2018-05-21T15:37:54.793

1

Use aliases in your sql query. In this case it's fullname for the CONCAT(..) result.

$query="SELECT `contact_company`.`contact_id`, CONCAT(`surname`,' '.`fname`) as fullname, `contact_company`.`company_id` FROM `seta`.`contact_company` AS `contact_company`, `seta`.`contacts` AS `contacts` WHERE `contact_company`.`contact_id` = `contacts`.`contact_id` AND `contact_company`.`company_id` ='".$company_id."' ";

In your while loop replace with this:

$contact_name=$row["fullname"];

Also you ended your first statement with colon : instead of semi-colon ;

Change your first line to:

$company_id=$_POST['company'];

edited May 21 '18 at 15:37

answered May 21 '18 at 15:18

Karlo Kokkak

3,674
4
18
33

To bad this answer still maintains the possible SQL injection on the $company_id variable – Raymond Nijland May 21 '18 at 15:19
It's a simple script bro. Its solution is only one fold. Weird, its solution is the same for everyone. – Karlo Kokkak May 21 '18 at 15:56

ajmedway · Answer 2 · 2018-05-21T15:51:20.813

1

It appears that the main problem here is that you ended a php statement with a colon instead of a semi-colon, i.e. $_POST['company']:.

Additionally, there is a problem in your MySQL CONCAT function. In your code it has:

`CONCAT(`surname`,' '.`fname`)`

This should separate each string literal or field with a comma. Additionally, you have a leading period . before fname, so like:

`CONCAT(`surname`, ' ', `fname`)`

Or if the fname field appears in more than one of the tables thus needs an alias, you should specify this:

`CONCAT(`surname`, ' ', `contacts`.`fname`)`

Fixed version below, please try this.

    $company_id = $_POST['company'];
    $query = "SELECT `contact_company`.`contact_id`, CONCAT(`surname`, ' ', `fname`) AS `fullname`, `contact_company`.`company_id` FROM `seta`.`contact_company` AS `contact_company`, `seta`.`contacts` AS `contacts` WHERE `contact_company`.`contact_id` = `contacts`.`contact_id` AND `contact_company`.`company_id` ='" . mysqli_real_escape_string($company_id) . "' ";

    $result = mysqli_query($connection, $query) or die("Query to get data from contact_company table failed: " . mysqli_connect_error());


    while ($row = mysqli_fetch_array($result)) {
        $contact_id = $row["contact_id"];
        $company_id = $row["company_id"];
        $contact_name = $row["fullname"];

        echo "<option value=\"$contact_id\">
             $contact_name
             </option>";
    }

edited May 21 '18 at 15:51

answered May 21 '18 at 15:19

ajmedway

1,492
14
28

To bad this answer still maintains the possible SQL injection on the $company_id variable – Raymond Nijland May 21 '18 at 15:20
Not my code, and not related to his concat issue, but you are right - I didn't see that. Added in a remedy for posterity. – ajmedway May 21 '18 at 15:24
@RaymondNijland as I said, the $company_id comes from a dropdown menu, no injection is possible. – Tommaso Lazzari May 21 '18 at 15:27
@ajmedway the code works fine without the concat function – Tommaso Lazzari May 21 '18 at 15:28
1

"RaymondNijland as I said, the $company_id comes from a dropdown menu, no injection is possible." Every $_GET, $_POST variables can not be trusted and can contain SQL injection attack vectors or other attack vectors – Raymond Nijland May 21 '18 at 15:29
@TommasoLazzari "the code works fine without the concat function" - ah! Just seen it now, revising my answer :) – ajmedway May 21 '18 at 15:32
@TommasoLazzari - updated, please check. Additionally, I can confirm that RaymondNijland is correct in his assertions. For example, anyone could go into your dropdown menu in a browser's dom inspector e.g. Google Chrome inspector and manipulate the options there and submit bogus data. NEVER trust user submitted data. Note my answer uses this: http://php.net/manual/en/mysqli.real-escape-string.php - but ideally you should be using [the more modern approach - PDO](http://php.net/manual/en/ref.pdo-mysql.php) – ajmedway May 21 '18 at 15:43
@ajmedway thanks for the comment, I will go back and check – Tommaso Lazzari May 23 '18 at 04:15

score 0 · Answer 3 · answered May 21 '18 at 15:16

0

Your CONCAT syntax is wrong. Please see below for the right version.

$query="SELECT `contact_company`.`contact_id`, CONCAT(`surname`,' ',`fname`), `contact_company`.`company_id` FROM `seta`.`contact_company` AS `contact_company`, `seta`.`contacts` AS `contacts` WHERE `contact_company`.`contact_id` = `contacts`.`contact_id` AND `contact_company`.`company_id` ='".$company_id."' ";

Hope this helps.

answered May 21 '18 at 15:16

Kishen Nagaraju

2,062
9
17

To bad this answer still maintains the possible SQL injection on the $company_id variable. – Raymond Nijland May 21 '18 at 15:18
1

@Raymond - I am just helping out with the issue that the question has mentioned. The SQL injection part may not be implemented in the application. So please suggest it. Also you got your answer above in the comments. – Kishen Nagaraju May 21 '18 at 15:25

score 0 · Answer 4 · answered May 21 '18 at 15:21

You have problem with your sql CONCATE function, this should be :

CONCAT(surname,' ',fname)

 $company_id=$_POST['company']:
    $query="SELECT `contact_company`.`contact_id`, CONCAT(`surname`,' ',`fname`), `contact_company`.`company_id` FROM `seta`.`contact_company` AS `contact_company`, `seta`.`contacts` AS `contacts` WHERE `contact_company`.`contact_id` = `contacts`.`contact_id` AND `contact_company`.`company_id` ='".$company_id."' ";

    $result=mysqli_query($connection,$query) or die ("Query to get data from contact_company table failed: ".mysqli_connect_error());


    while ($row=mysqli_fetch_array($result)) {
        $contact_id=$row["contact_id"];
        $company_id=$row["company_id"];
        $contact_name=$row["CONCAT(`surname`,' ',`fname`)"];

        echo "<option value=\"$contact_id\">
             $contact_name
             </option>";
        }

To bad this answer still maintains the possible SQL injection on the $company_id variable — Raymond Nijland, May 21 '18 at 15:21

Concat function in the Ajax response Php file breaks the script

4 Answers4