The correct way to avoid SQL injection attacks, no matter which database you use, is to separate the data from SQL, so that data stays data and will never be interpreted as commands by the SQL parser. It is possible to create SQL statement with correctly formatted data parts, but if you don't fully understand the details, you should always use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.
You basically have two options to achieve this:
Using PDO (for any supported database driver):
$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
$stmt->execute([ 'name' => $name ]);
foreach ($stmt as $row) {
// Do something with $row
}
Using MySQLi (for MySQL):
$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name); // 's' specifies the variable type => 'string'
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
// Do something with $row
}
If you're connecting to a database other than MySQL, there is a driver-specific second option that you can refer to (for example, pg_prepare()
and pg_execute()
for PostgreSQL). PDO is the universal option.
Correctly setting up the connection
Note that when using PDO to access a MySQL database real prepared statements are not used by default. To fix this you have to disable the emulation of prepared statements. An example of creating a connection using PDO is:
$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'password');
$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
In the above example the error mode isn't strictly necessary, but it is advised to add it. This way the script will not stop with a Fatal Error
when something goes wrong. And it gives the developer the chance to catch
any error(s) which are throw
n as PDOException
s.
What is mandatory, however, is the first setAttribute()
line, which tells PDO to disable emulated prepared statements and use real prepared statements. This makes sure the statement and the values aren't parsed by PHP before sending it to the MySQL server (giving a possible attacker no chance to inject malicious SQL).
Although you can set the charset
in the options of the constructor, it's important to note that 'older' versions of PHP (before 5.3.6) silently ignored the charset parameter in the DSN.
Explanation
The SQL statement you pass to prepare
is parsed and compiled by the database server. By specifying parameters (either a ?
or a named parameter like :name
in the example above) you tell the database engine where you want to filter on. Then when you call execute
, the prepared statement is combined with the parameter values you specify.
The important thing here is that the parameter values are combined with the compiled statement, not an SQL string. SQL injection works by tricking the script into including malicious strings when it creates SQL to send to the database. So by sending the actual SQL separately from the parameters, you limit the risk of ending up with something you didn't intend.
Any parameters you send when using a prepared statement will just be treated as strings (although the database engine may do some optimization so parameters may end up as numbers too, of course). In the example above, if the $name
variable contains 'Sarah'; DELETE FROM employees
the result would simply be a search for the string "'Sarah'; DELETE FROM employees"
, and you will not end up with an empty table.
Another benefit of using prepared statements is that if you execute the same statement many times in the same session it will only be parsed and compiled once, giving you some speed gains.
Oh, and since you asked about how to do it for an insert, here's an example (using PDO):
$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)');
$preparedStatement->execute([ 'column' => $unsafeValue ]);
Can prepared statements be used for dynamic queries?
While you can still use prepared statements for the query parameters, the structure of the dynamic query itself cannot be parametrized and certain query features cannot be parametrized.
For these specific scenarios, the best thing to do is use a whitelist filter that restricts the possible values.
// Value whitelist
// $dir can only be 'DESC', otherwise it will be 'ASC'
if (empty($dir) || $dir !== 'DESC') {
$dir = 'ASC';
}
Modern HTML5 Techniques for changing classes
Modern browsers have added classList which provides methods to make it easier to manipulate classes without needing a library:
document.getElementById("MyElement").classList.add('MyClass');
document.getElementById("MyElement").classList.remove('MyClass');
if ( document.getElementById("MyElement").classList.contains('MyClass') )
document.getElementById("MyElement").classList.toggle('MyClass');
Unfortunately, these do not work in Internet Explorer prior to v10, though there is a shim to add support for it to IE8 and IE9, available from this page. It is, though, getting more and more supported.
Simple cross-browser solution
The standard JavaScript way to select an element is using document.getElementById("Id")
, which is what the following examples use - you can of course obtain elements in other ways, and in the right situation may simply use this
instead - however, going into detail on this is beyond the scope of the answer.
To change all classes for an element:
To replace all existing classes with one or more new classes, set the className attribute:
document.getElementById("MyElement").className = "MyClass";
(You can use a space-delimited list to apply multiple classes.)
To add an additional class to an element:
To add a class to an element, without removing/affecting existing values, append a space and the new classname, like so:
document.getElementById("MyElement").className += " MyClass";
To remove a class from an element:
To remove a single class to an element, without affecting other potential classes, a simple regex replace is required:
document.getElementById("MyElement").className =
document.getElementById("MyElement").className.replace
( /(?:^|\s)MyClass(?!\S)/g , '' )
/* Code wrapped for readability - above is all one statement */
An explanation of this regex is as follows:
(?:^|\s) # Match the start of the string or any single whitespace character
MyClass # The literal text for the classname to remove
(?!\S) # Negative lookahead to verify the above is the whole classname
# Ensures there is no non-space character following
# (i.e. must be the end of the string or space)
The g
flag tells the replace to repeat as required, in case the class name has been added multiple times.
To check if a class is already applied to an element:
The same regex used above for removing a class can also be used as a check as to whether a particular class exists:
if ( document.getElementById("MyElement").className.match(/(?:^|\s)MyClass(?!\S)/) )
### Assigning these actions to onclick events:
Whilst it is possible to write JavaScript directly inside the HTML event attributes (such as onclick="this.className+=' MyClass'"
) this is not recommended behaviour. Especially on larger applications, more maintainable code is achieved by separating HTML markup from JavaScript interaction logic.
The first step to achieving this is by creating a function, and calling the function in the onclick attribute, for example:
<script type="text/javascript">
function changeClass(){
// Code examples from above
}
</script>
...
<button onclick="changeClass()">My Button</button>
(It is not required to have this code in script tags, this is simply for the brevity of example, and including the JavaScript in a distinct file may be more appropriate.)
The second step is to move the onclick event out of the HTML and into JavaScript, for example using addEventListener
<script type="text/javascript">
function changeClass(){
// Code examples from above
}
window.onload = function(){
document.getElementById("MyElement").addEventListener( 'click', changeClass);
}
</script>
...
<button id="MyElement">My Button</button>
(Note that the window.onload part is required so that the contents of that function are executed after the HTML has finished loading - without this, the MyElement might not exist when the JavaScript code is called, so that line would fail.)
JavaScript Frameworks and Libraries
The above code is all in standard JavaScript, however, it is common practice to use either a framework or a library to simplify common tasks, as well as benefit from fixed bugs and edge cases that you might not think of when writing your code.
Whilst some people consider it overkill to add a ~50 KB framework for simply changing a class, if you are doing any substantial amount of JavaScript work or anything that might have unusual cross-browser behavior, it is well worth considering.
(Very roughly, a library is a set of tools designed for a specific task, whilst a framework generally contains multiple libraries and performs a complete set of duties.)
The examples above have been reproduced below using jQuery, probably the most commonly used JavaScript library (though there are others worth investigating too).
(Note that $
here is the jQuery object.)
Changing Classes with jQuery:
$('#MyElement').addClass('MyClass');
$('#MyElement').removeClass('MyClass');
if ( $('#MyElement').hasClass('MyClass') )
In addition, jQuery provides a shortcut for adding a class if it doesn't apply, or removing a class that does:
$('#MyElement').toggleClass('MyClass');
### Assigning a function to a click event with jQuery:
$('#MyElement').click(changeClass);
or, without needing an id:
$(':button:contains(My Button)').click(changeClass);
Best Answer
If you're running PHP on Windows, you can use the COM wrapper for PHP to interface with Skype4COM. I haven't tested it, but following the examples from the Skype4COM documentation, this should work:
However, this would use the instance of Skype running on your server. It is certainly impossible to interface with the instance of Skype running on your client's machine using client-side JavaScript. Some basic interfacing can be done with MSIE and VBScript, but I think that this way the functionality would be very limited as well, because it would be very insecure if it wasn't limited.