MySQLi->escape_string issue

[halojoy]

I have this method

    public function create_table($sql)
    {
        if (!$stmt = $this->prepare($sql))
            exit('Error: '.$this->error);
        return $stmt->execute();
    }

It works with

$sql = "CREATE TABLE IF NOT EXISTS users (
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
username TEXT,
password TEXT,
usertype TEXT,
email TEXT,
joined INTEGER )";
if (!$db->create_table($sql)) {
    echo 'error create table users';
}

But when I try to escape the $sql
with this

    public function create_table($sql)
    {
        $sql = $this->escape_string($sql);
        if (!$stmt = $this->prepare($sql))
            exit('Error: '.$this->error);
        return $stmt->execute();
    }

I get the following error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\nid INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,\nusername TEXT,\npassword TEXT' at line 1

It is because the mysqli->escape_string()
escapes the newlines.

Is there a workaround that lets me use the escape of $sql?

[dalecosp]

Have you tried removing the newlines?

$sql = $this->escape_string(trim(preg_replace('/\s\s+/', ' ', $sql)));

[NogDog]

You do not want to escape the entire SQL string -- only unknown values being used in it, e.g.:

$sql = "SELECT * FROM my_table WHERE first_name = '" . mysqli_escape_string($_GET['first_name']) . "'";

(or [FONT=Courier New]$this->escape_string($_GET['first_name'])[/FONT] or [FONT=Courier New]$db->escape_string($_GET['first_name'])[/FONT], depending on how/where you are using it)

[dalecosp]

NogDog;11065213 wrote:

You do not want to escape the entire SQL string -- only unknown values being used in it, e.g.:

I'm not sure there are any unknown values ...

[NogDog]

dalecosp;11065215 wrote:

I'm not sure there are any unknown values ...

In which case there would be no need to use it. (shrug) If you escape the entire SQL string, then it thinks it's supposed to make everything in it not be treated as SQL keywords, but as one long string literal.

[benanamen]

Better yet, put on your big boy pants and use PDO with Prepared Statements and you wont have problems like this. Here is a tutorial to get you going
https://phpdelusions.net/pdo

[pbismad]

And since there are no data values being put into the sql statement, there's also no good reason to prepare it. This just adds an extra round-trip communication between php and the database server. Just use the ->query() method.

Also, you should be using exceptions for database statement errors. This will eliminate the need to have error handling logic (for most queries) in your code, which you are currently missing for the execute method call. Both the prepare and execute call can produce errors and both should be handled in the same way. By using exceptions for errors, and let php catch the exception (for most queries), will allow the sensitive error information to either be displayed or logged, simply by changing php's display_errors and logged_errors settings. You should only display the raw error information when learning, developing, and debugging code/queries. When on a live/public server, you should log the error information.

[halojoy]

I thanks for all the answers.
I have read it all.
A search with google gave me the impression there is no need to escape when using prepare.
Prepare itself does escaping. And double escaping creates the problem.
So, in line even with comments here, I will do no escaping.
And so the code will work well just the same.

The same issue is here.
This method is used for INSERT, UPDATE and DELETE:

    

    public function put($sql)
    {
        if (!$stmt = $this->prepare($sql))
            exit('Error: '.$this->error);
        $stmt->execute();
        $this->insert = $stmt->insert_id;
        return $this->insert;
    }
    

Evenso here I do not use escape now.
Because adding escape will create error in INSERT syntax.

[Weedpacket]

You prepare the statement before you add the values, then you add the values. You can do it before you execute the query, or you can supply the values when you execute it. Either way, once you've prepared the statement, you don't need to do any manual escaping; the statement object itself knows enough to escape the values you give it.