-2

I've been developing a website and since the beggining I've been thinking of how to solve this problem.

So here it is:

My website as alot of input fields, and with that, I came to notice that if a user uses ' there's an SQL error. I've search for similiar problems and i did not find any, but I believe I might be searching the wrong way.

This is how I execute my queries:

I created a function for my queries:

function query($sql) {  
    $stmt = $conn->prepare($sql);
    $stmt->execute();
}

And then I use it like this:

$sql = "INSERT INTO users(fname,lname,username) VALUES('$fname','$lname','$username')";
$stmt = $dbconn->query($sql); // $dbconn is the variable of my db connection class

I thought about using str_replace to replace the ' with a \ but then if a user uses \ then it will be converted to a ' and it's not that great of a solution.

Ruben Perdigao
  • 60
  • 5
  • try `$sql = "INSERT INTO users(fname, lname, username) VALUES(" . $fname . "," . $lname . "," . $username . ")";` – Jean-Marc Zimmer Nov 07 '18 at 16:07
  • 5
    Parameterize your query and not only will this issue go away, but you will also keep from suffering a sql injection attack. It would be a real shame if I told you my username was `');DROP TABLE users;--` – JNevill Nov 07 '18 at 16:07
  • To elaborate on the comment by @JNevill - you can pass an array of params as an argument to `execute()` so for a prepared statement for a query like `INSERT INTO users (fname, lname, username) VALUES (:fname, :lname, :username)` you can do `$stmt->execute(['fname' => 'foo', 'lname' => 'bar', 'username' => 'foo.bar']);`. You can also use positional params (`?`) with an indexed array of values. – Darragh Enright Nov 07 '18 at 16:19

1 Answers1

-2

Use Stored Procedures and pass in the data as parameters.

Also, only grant "Execute" permission in the DB to the user your website logs in as.

This will prevent hackers from running any database queries/updates that aren't in your stored procedure(s) and will also prevent SQL Injection attacks and improve performance.

https://xkcd.com/327/

Terry Carmen
  • 3,720
  • 1
  • 16
  • 32