mysqli_query string less long

Question

I have just started to learn MySQL and its syntax. My question is:

Are there some options to do this in a more readable way and use less quotes and escaping slashes? It seems to me 'a little stupid' how it is written so far. And also I think this is the problem why mysqli_escape_string() isn't doing what I expect:

mysqli_set_charset($connection, 'utf8');
$string = "INSERT INTO pass_words(site_name,user_id,pass_id,email,question,answer) VALUE ("
                .
                ' \' '.$site.' \' '
                . ','.
                ' \' '
                .$acc_name.' \' '
                . ','.
                ' \' '
                .$pass.' \' '
                . ','.
                ' \' '
                .$email.' \' '
                . ','.
                ' \' '
                .$question.' \' '
                . ','
                .' \' '
                . ''.$answer.' \' '.')' ;
if($query = mysqli_query($connection, mysqli_escape_string($connection, $string))){
    echo '<div style="color: #00FF99; margin: 0 0 20px 0;"> The Account Information has been added correctly.</div>';
}

score 1 · Answer 1 · edited May 23 '17 at 12:22

1

There's no need to make a compact but unreadable mess.

Make your SQL query as lofty as you want.
Use ? placeholders and parameter binding.
And use any wrapper function for parameter binding (mysqli by itself is horrible to use).

Something like:

xyz_query("
     INSERT INTO pass_words
         (site_name, user_id, pass_id, email, question, answer)
     VALUES
         (?, ?, ?, ?, ?, ?)
     ", # ↑      ↑       ↑
     [$site, $acc_name, $pass, $email, $question, $answer]
);

edited May 23 '17 at 12:22

Community

1
1

answered Aug 08 '15 at 10:20

mario

144,265
20
237
291

This helped me, too. Thanks for the answer and the advice. – Bogdan Bogdanov Aug 08 '15 at 11:57
Is binding a better practise than just including the variables in a string? Is there any benefit? – James111 Aug 16 '15 at 12:01
1

@James111 It's both prettier to look at, and more reslient against arbitrary values (which is what makes it more secure). See http://i.stack.imgur.com/M7PXp.png and [Why shouldn't I use mysql\_\* functions in PHP?](http://stackoverflow.com/a/20767765) – mario Aug 16 '15 at 12:56

score 0 · Answer 2 · answered Aug 08 '15 at 10:20

0

$string = "INSERT INTO `pass_words`(`site_name`, `user_id`, `pass_id`, `email`, `question`, `answer`) 
VALUES('$site', '$acc_name', '$pass', '$email', '$question', '$answer')";

answered Aug 08 '15 at 10:20

Al Amin Chayan

2,460
4
23
41

score 0 · Accepted Answer · answered Aug 08 '15 at 10:34

0

Here is another way using SET.

$string = "INSERT INTO pass_words SET site_name = '$site', user_id = '$acc_name', pass_id = '$pass', email = '$email', question = '$question', answer = '$answer'";

answered Aug 08 '15 at 10:34

Kamal Joshi

1,298
3
25
45

this worked but i needed to put manually for all variables - $connection->real_escape_string(), before using the $string. thanks – Bogdan Bogdanov Aug 08 '15 at 11:52

mysqli_query string less long

3 Answers3