Yii2 error on sql execute

Question

I am trying to save message in MySQL but failure is what i get.

public function actionChat()
    {
        $message = \Yii::$app->request->get('message');
        $user_id = \Yii::$app->user->identity->id;
        \Yii::$app->db->createCommand("INSERT INTO chat(message_content, user_id) VALUES ($message, $user_id)")->execute();

        return $message;
    }

When i write the same query in phpmyadmin it works fine but not in my controller. I also tried to put some string in the query like

\Yii::$app->db->createCommand("INSERT INTO chat(message_content, user_id) VALUES ('first message', $user_id)")->execute();

but result was the same. Can you give me advice where is my mistake? Thank you! AJAX:

$('body').on('click', '#chat-button', function () {
        var message = $('#chat-message').val();
        $.ajax({
            method : 'GET',
            url : '../site/chat?message=' + message,
            dataType : 'text',
            success : function ( data ) {
                alert(data);
            }
        }) ;
    });

GET http://letsblog/site/chat?message=fa 500 (Internal Server Error) but i think my ajax is fine. Will add it too. No other error is shown. Just it refuse to add the message in database :) — Toma Tomov
– Toma Tomov, Commented Apr 25, 2017 at 15:38

Pedro del Sol · Accepted Answer · 2017-04-26 16:48:03Z

2

Another approach would be to use a parameterized query; avoiding any quoting issues.

\Yii::$app->db->createCommand(
     "INSERT INTO chat(message_content, user_id)
      VALUES (:content, :userID)", 
      [':content' => $message, ':userID' => $user_id])->execute();

answered Apr 26, 2017 at 16:48

Pedro del Sol

2,8419 gold badges43 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Toma Tomov Over a year ago

Yes, i red some yii2 documentation :) Thank you!

ScaisEdge · Accepted Answer · 2017-07-28 05:57:39Z

0

If $message is string value you should use single queto in you sql statement ( and if $user_id is a string then add quote to this var too)

 \Yii::$app->db->createCommand("INSERT INTO chat(message_content, user_id)
           VALUES ('$message', $user_id)")->execute();

and be carefull the use of $var in SQL statements can implies sqlijnjection .. you should use param bindig for assign values

edited Jul 28, 2017 at 5:57

answered Apr 25, 2017 at 15:44

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

5 Comments

Toma Tomov Over a year ago

Correct! Thank you very much!

Toma Tomov Over a year ago

Ofcourse! I was just waiting 5 minutes to pass :) Stackoverflow rules :)

Phil Cairns Over a year ago

This is a very poor way of doing things as you are subject to SQL injection attacks. If the message was something like filler', 1); DROP TABLE chat; -- then you're pretty much toast.

ScaisEdge Over a year ago

@PhilCairns .. the answer if not for the way the OP use the var in SQL .. but is about the error message .. so you comment and downvote ishould not be attributed to my answer but should be made to the OP. Commenting and giving negative judgments based on aspects that are not introduced by the response is basically a symptom of incompetence. If you have a better answer to it .. if you do not have it you should not criticize others for things they did not do.

Phil Cairns Over a year ago

@scaisEdge, you have almost 60k reputation and I'd suggest that with that comes a certain level of responsibility. It costs nothing to add a bit extra that says "oh, and you might want to be careful with that, because someone could destroy your application simply by typing something into your web form, and even if they don't, you could get some really weird errors just because they typed a quote in their message". I didn't provide an answer, because Pedro's already provided a better one.

Collectives™ on Stack Overflow

Yii2 error on sql execute

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related